gpt4 book ai didi

vba - 添加缺失日期 VBA

转载 作者:行者123 更新时间:2023-12-01 19:44:48 26 4
gpt4 key购买 nike

我必须将缺失的日期插入一行,而不删除重复的日期(对于计费程序)。示例数据:

DATE
01/02/2016
02/02/2016
03/02/2016
03/02/2016
03/02/2016
06/02/2016
07/02/2016
08/02/2016

我的代码无限循环并删除重复的日期。为什么会出现这种情况?

Sub InsertMissingDates()

Dim i As Long
Dim RowCount As Long

i = 4

Do
If Cells(i, 1) + 1 <> Cells(i + 1, 1) Then
Rows(i + 1).Insert
Cells(i + 1, 1) = Cells(i, 1) + 1
End If
i = i + 1
Loop Until Cells(i + 1, 1) = "31.10.2016"

End Sub

最佳答案

这里是修改后的代码,并添加了注释以解决您的问题

Sub InsertMissingDates()

Dim i As Long
Dim RowCount As Long

i = 4

Do
'Use less then instead of <> so it doesn't flag duplicate cells
If Cells(i, 1) + 1 < Cells(i + 1, 1) Then
Rows(i + 1).Insert
Cells(i + 1, 1) = Cells(i, 1) + 1
End If
'Second check to add value if the next row is blank
If (Cells(i + 1, 1) = "") Then
Cells(i + 1, 1) = Cells(i, 1) + 1
End If

i = i + 1
'Changed the loop function from cells(i+1,1) to cells(i,1) since you already
'incremented i
'Also made the date check slightly more robust with dateserial
Loop Until Cells(i, 1).Value >= DateSerial(2016, 1, 30)

End Sub

关于vba - 添加缺失日期 VBA,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36723424/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com