CODEWITHSUNDEEP
excelvba
Joel Bastien
Joel Bastien

Reputation: 121

Why do I keep getting an error in my code?

I'm attempting my first VBA code and I keep getting a run time error at this specific place in my code: 

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

Here is the actual code: 

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

My goal is to go though the last cell of my row and find a cell with a specific date that has a cell to the right with a specific text. Then it would highlight the first cell in that row and loop on to the next row. I'm not too sure where I went wrong and why I am getting an error.

would appreciate the help

Upvotes: 1

Views: 195

Answers (1)

barrowc
barrowc

Reputation: 10679

The code is producing an error because ws isn't set to any actual worksheet. Here's how to fix this:

  • add Option Explicit as the first line in the module. This will let Excel catch any undeclared variables
  • declare ws as a variable of type Worksheet using a Dim statement. Also add declarations any other variables that we use later - i, rrr, ggg, bbb
  • make ws point to an actual worksheet using a Set statement

Putting this together gives us:

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

Upvotes: 2

Related Questions