excel VBA代码帮助-为每个缺少的日期添加一行,并在单元格中定义开始日期和结束日期

2uluyalo  于 2023-01-27  发布在  其他
关注(0)|答案(2)|浏览(144)

我刚开始编写VBA代码,需要一些帮助。
到目前为止,我有下面的代码,它为每个缺失的日期添加一行,但它不添加任何行的日期是在月底或月初缺失。有人能帮助重写代码,使它添加所有日期之间的开始和结束日期缺失。开始和结束日期将需要每月更新,所以需要很容易地改变,如单元格A2和B2的"摘要"工作表。同样值得注意的是,对于添加的每一行,它都会从下面的单元格复制数据。

Dim wks As Worksheet
Set wks = Worksheets("NAV_REPORT_FSIGLOB1")

Dim lastRow As Long
lastRow = Range("D2").End(xlDown).Row

For i = lastRow To 2 Step -1
    curcell = wks.Cells(i, 4).Value
    prevcell = wks.Cells(i - 1, 4).Value

    Do Until curcell - 1 <= prevcell
        wks.Rows(i).Copy
        wks.Rows(i).Insert xlShiftDown

        curcell = wks.Cells(i + 1, 4) - 1
        wks.Cells(i, 4).Value = curcell
    Loop
Next i

以下是更新前的数据示例

下面是我希望运行宏后的数据。

任何帮助都将不胜感激。

mnemlml8

mnemlml81#

Sub FillDates()

    Dim wks As Worksheet, i As Long, n As Long
    Dim dt1 As Date, dt2 As Date, x As Long, d As Long
    
    Set wks = Worksheets("NAV_REPORT_FSIGLOB1")
    With wks
        'make start 1st
        dt1 = .Cells(2, "D")
        If Day(dt1) > 1 Then
            .Rows(2).Copy
            .Rows(2).Insert xlShiftDown
            .Cells(2, "D") = DateSerial(Year(dt1), Month(dt1), 1)
            n = n + 1
        End If

        i = .Cells(.Rows.Count, "D").End(xlUp).Row
        Do
            .Cells(i, "D").Select
            dt1 = .Cells(i - 1, "D")
            dt2 = .Cells(i, "D")
            
            d = DateDiff("d", dt1, dt2)
            If d = 1 Then
                i = i - 1
            ElseIf d > 1 Then
                .Rows(i).Copy
                .Rows(i).Insert xlShiftDown
                .Cells(i, "D") = DateAdd("d", -1, dt2)
                n = n + 1
            ElseIf d < 1 Then
                MsgBox "Date sequence error", vbCritical
                Exit Sub
            End If
            
            ' escape infinite loop
            x = x + 1
            If x > 100 Then
                 MsgBox "Too many iterations > 100", vbCritical
                 Exit Sub
            End If
        Loop While i > 2
    
    End With
    MsgBox n & " rows added"

End Sub
d7v8vwbk

d7v8vwbk2#

使用尽可能多的现有代码,您可以使用以下代码。使用A1中有开始日期,A2中有结束日期的摘要表进行测试。

Sub test_this()
 
    Dim wks As Worksheet, ssh As Worksheet
    Set wks = Worksheets("NAV_REPORT_FSIGLOB1")
    Set ssh = Worksheets("SUMMARY")
    Dim lastRow As Long, start_date As Date, end_date As Date, curcell As Date
    
    lastRow = Range("D2").End(xlDown).Row
    start_date = ssh.Range("A1") - 1
    end_date = ssh.Range("A2")
    
    With wks.Cells(lastRow, 4)
        If .Value < end_date Then
            .EntireRow.Copy
            .EntireRow.Insert xlShiftDown
            lastRow = lastRow + 1
            .Value = end_date
        End If
    End With

    For i = lastRow To 2 Step -1
        curcell = wks.Cells(i, 4).Value
        If i = lastRow Then curcell = end_date
        prevcell = wks.Cells(i - 1, 4).Value
        If i = 2 Then prevcell = start_date
        Do Until curcell - 1 <= prevcell
            wks.Rows(i).Copy
            wks.Rows(i).Insert xlShiftDown
            curcell = wks.Cells(i + 1, 4) - 1
            wks.Cells(i, 4).Value = curcell
        Loop
    Next i

End Sub

相关问题