0

I'm trying to streamline the code of a VBA project in Excel and I run into a problem when setting a range: 

Sub Test()
Dim lRow As Long, lCol As Long
Dim TSheet As Variant, TSheets As Variant
Dim rng As Range

TSheets = Array("Sheet1", "Sheet2", "Sheet3")

For Each TSheet in TSheets
lRow = ThisWorkbook.Sheets(TSheet).Cells(Rows.Count, "B").End(xlUp).Row + 1
lCol = ThisWorkbook.Sheets(TSheet).Cells(lRow - 1, "B").End(xlToRight).Column
Set rng = ThisWorkbook.Sheets(TSheet).Range(Cells(2, 2), Cells(lRow, lCol))
Next TSheet

End Sub

I get Error 1004 "Application-defined or object-defined error". Set rng = ThisWorkbook.Sheets(TSheet).Cells(lRow, lCol) works, so there must be a problem with the Range(Cells(),Cells()) syntax. Could you help me with that?

edit: Thanks to GSerg (thanks a lot!) I got it to work, the key is I have to write Set rng = ThisWorkbook.Sheets(TSheet).Range(ThisWorkbook.Sheets(TSheet).Cells(2, 2), ThisWorkbook.Sheets(TSheet).Cells(lRow, lCol)). However, I still don't get why my original code got an error message. Shouldn't cells() automatically refer to the single sheet I am working on?

Alex
  • 515
  • 5
  • 19
  • 1
    *Shouldn't `cells()` automatically refer to the single sheet I am working on?* - No. Never assume. – CLR Apr 23 '19 at 11:22
  • I get that, but what is it referring to then? Why is Cells() working and Range(Cells(), Cells()) isn't? I just want to understand the logic behind it. – Alex Apr 23 '19 at 12:10
  • If you refer to any range without qualifying it, you're at the mercy of Excel choosing. I'm sure someone somewhere knows *how* it chooses, but to the naked eye, it appears to be pretty random. – CLR Apr 23 '19 at 13:47
  • 1
    @CLR It is correctly explained in the [linked answer](https://stackoverflow.com/a/17790711/11683), in the bulleted list at the beginning. – GSerg Apr 23 '19 at 16:14
  • @GSerg I've seen that and I know I'm being obtuse here, but I don't see how this leads to an error in my case. Could you spell it out for me? Also, what exactly is meant by "In a sheet's class module"? Is that when writing Sheets("Sheet1").Cells, i.e. explicitly referring to a sheet? – Alex Apr 25 '19 at 09:34
  • 1
    @Alex If the executing code is located inside a worksheet's code module, unqualified `Cells` in that code resolves to `.Cells` due to scope precedence. If the executing code resides somewhere else (a standard module, a user form, a `ThisWorkbook` code module), then unqualified `Cells` in such code resolves to `Application.Cells`, which is a shorthand for `Application.ActiveSheet.Cells`. – GSerg Apr 25 '19 at 09:37

0 Answers0