使用日期的For循环编译错误:语法错误 [英] For loop using dates Compile Error: Syntax Error

问题描述

我正在尝试运行一个脚本，该脚本将跳过特定列的所有行.然后，它将这些列中的日期与设置的日期进行比较.如果日期大于日期，则删除该行.我遇到的错误称为编译错误:语法错误".

I am trying to run a script that will skim through all rows for a certain column. Then it will compare the dates in these columns with a set date. If the dates is larger than it deletes the row. The error I'm getting is called Compile Error: Syntax Error.

Sub removewrongyear()
Dim i As Integer

For i = 2 To 635475
 If Data.Cells(i,20).Value > DATE(2018,12,31) Then
Rows(i).EntireRow.Delete
Next i
End Sub

推荐答案

Yuo需要像@braX所说的那样向后退一步.同样指定的数字超出了 Integer 变量类型的容量.在VBA中，最好是始终将变量声明为 Long .

Yuo need to step backwards as @braX stated. Also specified number exceeds Integer variable type capacity. In VBA it is a good practice for ingeter values to always declare variable as Long.

范围数据"未设置.我用活动工作表代替了它.也未指定变量YearA.我给它分配了2018年的价值.日期函数使用不正确，您打算使用DateSerial.

Range "Data" is nowhere set. I replaced it with reference to active worksheet. Variable YearA is also not specified. I assigned value of 2018 to it. Date function is incorrectly used, you meant to use DateSerial.

始终在代码之上放置 Option Explicit 来捕获错误.这里真的有很多.

Always put Option Explicit on top of the code to catch errors. There were really many here.

Option Explicit

Sub removeWrongYear()

    Dim i As Long, yearA as Long

    yearA = 2018

    With ActiveSheet
        For i = 635475 to 2 Step -1
            If .Cells(i,20).Value > DateSerial(yearA,12,31) Then .Rows(i).EntireRow.Delete       
        Next i
    End With

End Sub

这是一个基于数组的快速版本，其中所有行都被一次删除:

Here is a fast version, based on arrays, with all rows deleted at once:

Option Explicit
Option Base 1 'row and column index will match array index

Sub removeWrongYear()

    Dim i As Long, yearA As Long, rowsCnt As Long
    Dim rowsToDelete As Range
    Dim vData As Variant

    yearA = 2018

    With ActiveSheet

        '1st to 635475 row, 20th column
        vData = Range(.Cells(1, 20), .Cells(635475, 20))

        For i = UBound(vData) To 2 Step -1
            If vData(i, 1) > DateSerial(yearA, 12, 31) Then
                rowsCnt = rowsCnt + 1

                If rowsCnt > 1 Then
                    Set rowsToDelete = Union(rowsToDelete, .Rows(i))
                ElseIf rowsCnt = 1 Then
                    Set rowsToDelete = .Rows(i)
                End If

            End If
        Next i

    End With

    If rowsCnt > 0 Then
        Application.ScreenUpdating = False
        rowsToDelete.EntireRow.Delete
        Application.ScreenUpdating = True
    End If

End Sub

这篇关于使用日期的For循环编译错误:语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！