For循环,变量结尾为整数 [英] For loop with variable ending integer
问题描述
我有40,000行数据的数据集.设置我的代码,以便它检查第n + 1行中的日期是否比第n行中的日期晚1天.如果第n行和第n + 1行中的日期未按正常的时间顺序排列,则它将添加一行包含该日期的空白数据.
I have a dataset of 40,000 rows of data. My code is set so that it checks if the date in row n+1 is 1 day after the date in row n. If the dates in rows n and n+1 do not follow in normal chronological order, then it adds a row with blank data for that date.
我的问题是,因为我随行添加行,所以我不知道for循环的终止范围应该是多少.我还尝试设置一个非常大的范围,例如对于n = 2到50000".但这给了我一个溢出错误.
My issues is that because I am adding rows along as I go, I have no idea what the ending range my for loop should have. I also tried just setting a really large range like "For n = 2 to 50000". But this gives me an overflow error.
这是我的代码:
Sub MissingDates()
Dim n As Integer
Worksheets("sheet1").Activate
For n = 2 To 40000
If Cells(n, 2).Value <> Cells(n + 1, 2).Value - 1 Then
Cells(n + 1, 2).EntireRow.Insert Shift:=xlShiftDown
Cells(n + 1, 2) = Cells(n, 2) + 1
End If
Next
End Sub
在此先感谢您的帮助.
推荐答案
带符号的整数不能达到40,000,因此您应该自下而上地工作.
A signed integer does not reach 40,000 and you should work from the bottom up.
Option Explicit
Sub MissingDates()
Dim n As Long, m As Long
With Worksheets("sheet1")
For n = .Cells(.Rows.Count, "B").End(xlUp).Row - 1 To 2 Step -1
For m = .Cells(n + 1, "B").Value2 - 1 To .Cells(n, "B").Value2 + 1 Step -1
.Cells(n + 1, 2).EntireRow.Insert Shift:=xlShiftDown
.Cells(n + 1, 2) = m
Next m
Next n
End With
End Sub
这篇关于For循环,变量结尾为整数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!