VBA nonsense pointing at the wrong sheet?

Question

Some strange things are happening to me in VBA. Somedays I am coding and everything works fine, then I go out of the code and the next day when I want to run the exact same code from the day before the code doesn't go into the loop, but jumps directly into end sub().

I tried to use the Activate sheet function, it seemed to work for 10 seconds and then afterwards suddenly it didn't work again which means my code didn't go into the loop again. The reason why I was using Activate sheet function was because I was afraid that my code was pointing at the wrong excel file / sheet. I have a lot of excel files and all of the sheets in those excel files are called Sheet1 could that create problems??

To give you a concrecte example, say I day one use the following code to loop through a column:

Sub stuff()

' Sheet1.Activate

lngLastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

Set sheet1ArkRng = Worksheets("Sheet1").Range("A1:Z" & lngLastRow)
Set offsheetarkrng = Worksheets("Sheet1").Range("L5")

Dim i As Integer

For i = 3 To sheet1ArkRng.Rows.Count
    
    sheet1VirkNavn = sheet1ArkRng.Cells(RowIndex:=i, ColumnIndex:="C").Value

Next i

End Sub

Then day 1 everything works fine and the variable sheet1VirkNavn shows me the value of the rows in column C. It can be seen when I debug.

Then day 2 once I debug the exact same code from the exact same file it goes to

"For i = 3 To sheet1ArkRng.Rows.Count"

and then jumps directly into

End sub

@Wander Nauta: Being in the same workbook, any sheet has the same number of rows... — FaneDuru
@FaneDuru: So what you are telling me is that lngLastRow shouldn't use A if all rows in column A are empty? — S Jensen
Is the correct workbook active when you run the code? Worksheets("Sheet1") will just look at whatever book is currently active. — Darren Bartrup-Cook
If all rows in column A are empty it will return 1 and your loop will not do anything (For i = 3 to 1). — Darren Bartrup-Cook
If you are dealing with lots of workbooks, you should specify not only the Worksheet, but also the workbook. Your code is accessing Sheet1 of the ActiveWorkbook - maybe not what you expect. — FunThomas

S Jensen S Jensen · Accepted Answer · 2021-04-28T11:59:46

Problem solved:

I've learned what

lngLastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

means. Now it all makes sense. If you're using the above code to loop through last row you should use the same letter for the column you want to loop through...

So the case was my code wasn't getting into the loop simply because my "A" column was empty.

VBA nonsense pointing at the wrong sheet?

1 Answers