0

I have a Form with many Subs. Workbook has many sheets. When I start the Form when the active sheet is CodesSheet, using any of the For Each rCell in the code below work OK. When I start the code from a different sheet, code runs OK when using the second line -.Range("L2:L4125")-, but fails with the first one -.Range(Cells(2, 12), Cells(LastRowCodes, 12)).

Message is Run time error 1004 - Method Range of object _worksheet failed. But on debug, mouse over LastRowCodes shows the proper value (4125).

Anyone can spot the error?

For Each rCell In CodesSheet.Range(Cells(2, 12), Cells(LastRowCodes, 12)) '-->Error here, although LastRowCodes is correct
'For Each rCell In CodesSheet.Range("L2:L4125") '--> If using this instead of the above, no error

    'Do Stuff

Next rCell
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
CMArg
  • 1,525
  • 3
  • 13
  • 28

1 Answers1

1

Cells without a Worksheet reference means ActiveSheet is assumed.

Use a With...End With block to fully qualify the Worksheet - note the periods.

With CodesSheet
    For each rCell in .Range(.Cells(2, 12), .Cells(LastRowCodes, 12))
        ....
    Next rCell
End With
BigBen
  • 46,229
  • 7
  • 24
  • 40
  • Thats it! I thought the previous `CodesSheet.Range` was enought. I've been dealing with this silliness for hours, before posting here. Thanks a lot. – CMArg Oct 03 '18 at 22:58