Range(Cells(xxx,yyy)) not working when I reference another Workbook?

[dm321321]

Hi all,

I isolated my issue to the following piece of code

Dim MetOceanWorkbook As Workbook
Dim MetOceanSheet As Worksheet
Dim RowIni, InputNcol as Variant

Application.ScreenUpdating = False
Set TWorkbook = Workbooks.Open(Path & "\" & "T.xlsx")
Set TSheet = TWorkbook.Worksheets.Item(2)
    
For jj = InputNcol(0) + ii * 4 To InputNcol(2) + ii * 4
        
     RowIni = TSheet.Cells.Range(Cells(1, jj), Cells(Rows.Count, jj)).Find(What:="*", _
     After:=Cells(1, jj), LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row
        
Next

It basically opens another workbook and gives the first non-empty cell from column jj

In short, this part of the code:
- Does not work as it is.
- Works when no external workbook is involved (It looks in the same worksheet than the macro)
- Works if I replace Range(Cells(1, jj), Cells(Rows.Count, jj)) and After:=Cells(1, jj) for Range("E:E") and Range("E1"), respectively.

This last option is less than ideal since I need to loop through different columns in an specific order.

Anyone knows what might be the issue?

Regards,

 

[RoryA]

You need to specify the same sheet for the Cells properties as for the Range:

TSheet.Range(TSheet.Cells(1, jj), TSheet.Cells(TSheet.Rows.Count, jj))

 

[Rick Rothstein]

Try

You need to specify the same sheet for the Cells properties as for the Range:

TSheet.Range(TSheet.Cells(1, jj), TSheet.Cells(TSheet.Rows.Count, jj))

I was just about to post that same message. You missed qualifying the Cells call for the "After:=" argument also. Also, I do not believe the first qualification on the Range is needed.

RowIni = Range(TSheet.Cells(1, jj), TSheet.Cells(Rows.Count, jj)).Find(What:="*", After:=TSheet.Cells(1, jj), _
     LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row

 

[RoryA]

I do not believe the first qualification on the Range is needed

It depends where the code is and I can't see any benefit in omitting it?

 

[Rick Rothstein]

It depends where the code is and I can't see any benefit in omitting it?

You are indicating something I might not be aware of. I always thought the qualifier in front of the main Range that contains two internal range references was not needed but your wording suggests there are locations when it is needed. Can you tell me when these times are or give me a reference that describes it? Thanks.

 

[Marc L]

Hi,​

​

Rick, yes it is not needed except when the VBA procedure is located in a worksheet module which is not the same as the range​

if I well remember, to be tested and reverted …​

 

[RoryA]

Correct. In a worksheet module, Range is really Me.Range unless otherwise specified, and that will fail if the Cells references are on a different sheet.