excel - 为什么我的代码中不断出现错误？

Question

我正在尝试我的第一个 VBA 代码，并且在我的代码中的这个特定位置不断收到运行时错误：

lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row

这是实际的代码：

Sub Test_loop()

' Testing loop for highlighting

Dim lastrow As Long
Dim datevar As String

lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow
    datevar = Format(ws.Cells(i, 2), "mm/dd")
    If ws.Cells(i, 3) = "Received" And datevar = "11/24" Then
        Cells(i, 1).Interior.Color = RGB(rrr, ggg, bbb)
    End If
Next i

End Sub

我的目标是遍历我行的最后一个单元格并找到一个具有特定日期的单元格，该单元格右侧有一个带有特定文本的单元格。然后它将突出显示该行中的第一个单元格并循环到下一行。我不太确定我哪里出错了，为什么会出错。

将不胜感激

标签： excelvba

Answer 1

该代码产生错误，因为ws未设置为任何实际工作表。以下是解决此问题的方法：

• 添加Option Explicit为模块中的第一行。这将使 Excel 捕获任何未声明的变量
• ws使用语句声明为 Worksheet 类型的变量Dim。还要添加我们稍后使用的任何其他变量的声明 - i, rrr, ggg,bbb
• 使用语句ws指向实际工作表Set

把它放在一起给我们：

Option Explicit

Sub Test_loop()

' Testing loop for highlighting

Dim lastrow As Long
Dim datevar As String
' These variables weren't declared in the original code
Dim ws As Worksheet
Dim i As Integer
Dim rrr As Integer
Dim ggg As Integer
Dim bbb As Integer

' ws needs to be set to an actual sheet - Sheet1 is used here
' but replace this with the name of the actual sheet you need
'
' ws will be set to the worksheet called Sheet1 in whichever
' workbook is active when the code runs - this might not be
' the same workbook that the code is stored in
Set ws = Worksheets("Sheet1")

' For consistency, need to qualify Rows.Count with
' a worksheet
lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow
    datevar = Format(ws.Cells(i, 2), "mm/dd")
    If ws.Cells(i, 3) = "Received" And datevar = "11/24" Then
        Cells(i, 1).Interior.Color = RGB(rrr, ggg, bbb)
    End If
Next i

End Sub