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屋！