根据DateDiff插入行 [英] Insert Rows based on DateDiff

问题描述

我有一个电子表格，可用来跟踪我的课程。我需要将其设置为导出到日历中。我列出了所有班级的开始和结束日期。我希望能够使用日期差作为行数在每个列出的类下面插入行，然后将信息复制到具有相应日期的行。

I have a spreadsheet that I use to track my classes. I need to set it up for export into a calendar. I have all my classes listed with begin and end dates. I want to be able to insert rows below each listed class using the date difference as the number of rows and then copy the information to those rows with the respective dates.

I有以下代码会插入行，但会给我一个 1004错误。

I have the following code which inserts the rows, but then gives me a '1004' error.

Public Sub Format()
    Dim i As Long
    Dim d As Long

    LastRow = Worksheets("GCalExport").UsedRange.Rows.Count

    For i = 2 To LastRow
        d = DateDiff("d", Cells(i, "B"), Cells(i, "D"))
        Cells(i, 1).Offset(1).Resize(d).EntireRow.Insert
    Next i
End Sub

推荐答案

您会收到此错误，因为B列或D列（可能两者）都不包含日期，并且 DateDiff 失败。

You are getting this error because either column B or column D (possibly both) do not contain a date and the DateDiff fails.

当您插入几行然后移至下一行时，会发生这种情况。当然，新插入的行为空，并且在B列或D列中不包含日期（并且发生上述错误）。

This happens when you insert a couple of rows and then just move to the next row. Of course, the newly inserted row is empty and does not contain a date in column B or column D (and the above error occurs).

因此，您需要进行调整您的代码如下：

So, you need to adjust your code as follows:

Public Sub Format()

Dim i As Long
Dim d As Long
Dim LastRow As Long

With Worksheets("GCalExport")
    LastRow = .UsedRange.Rows.Count
    i = 2

    While i <= LastRow
        'Check if column B and column D actually contain a date
        If IsDate(.Cells(i, "B")) And IsDate(.Cells(i, "D")) Then
            d = DateDiff("d", .Cells(i, "B"), .Cells(i, "D"))
            .Cells(i, 1).Offset(1).Resize(d).EntireRow.Insert
            'Since you inserted d rows the next row to check is
            '  row i + d
            i = i + d
            '  furthermore the last row just got increased by
            '  d as well
            LastRow = LastRow + d
            'Move to the next row for processing
            i = i + 1
        Else
            'If column B and / or D do not contain a valid date then
            '  ignore that row and go to the next row.
            i = i + 1
        End If
    Wend
End With

End Sub

请注意注释以获取更多信息。

Note the comments for more information.

这篇关于根据DateDiff插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！