Error Declaring Range, but only using cells definition?

[StevenHolden]

Hi All

I have this code which works well and consistently, but I can't seem to use the Cells method without the error. I have no idea why, so can anyone tell me off the top of their head what the deal is? Hopefully someone can easily point out my stupidity to me...

Basically I am trying to declare 2 ranges which contain a set of IDs. One is on the worksheet the code is launched from "ElmTemps", the other is not. The troublesome range is "rNodeIDs"

The code which works:

    Dim wbkCurrent As Workbook, shtElmTemps As Worksheet, shtNodeTemps As Worksheet 'Setup worksheets
    Dim rElmIDs As Range, rNodeIDs As Range 'Setup ranges
    Set wbkCurrent = ActiveWorkbook
    Set shtNodeTemps = wbkCurrent.Worksheets("NodeTemps")
    Set shtElmTemps = wbkCurrent.Worksheets("ElmTemps")
    Set rNodeIDs = shtNodeTemps.Range("A8:A1000")
    Set rElmIDs = shtElmTemps.Range(Cells(8, 1), Cells(shtElmTemps.UsedRange.Rows.Count, 1))

The code which breaks:

    Dim wbkCurrent As Workbook, shtElmTemps As Worksheet, shtNodeTemps As Worksheet 'Setup worksheets
    Dim rElmIDs As Range, rNodeIDs As Range 'Setup ranges
    Set wbkCurrent = ActiveWorkbook
    Set shtNodeTemps = wbkCurrent.Worksheets("NodeTemps")
    Set shtElmTemps = wbkCurrent.Worksheets("ElmTemps")
    Set rNodeIDs = shtNodeTemps.Range(Cells(8, 1), Cells(1000, 1))
    Set rElmIDs = shtElmTemps.Range(Cells(8, 1), Cells(shtElmTemps.UsedRange.Rows.Count, 1))

The code is exactly the same except for the rNodeIDs definition.
If the bad line is commented the rElmIDs is Set correctly.

My question is why I cannot use the Cells(i,j) method? I assume it is because it is on a different worksheet, but I thought explicitly declaring everything should allow this.

I can get around it no problem by building the "A8:Axxx" string as I need, but it is nowhere near as useful or clean as the cells approach.

Can anyone clear up this seemingly fundamental hole in my knowledge?

Thanks in advance!

 

[p45cal]

see if one of:

Set rNodeIDs = shtNodeTemps.Range(shtNodeTemps.Cells(8, 1), shtNodeTemps.Cells(1000, 1))

or

Set rNodeIDs = Range(shtNodeTemps.Cells(8, 1), shtNodeTemps.Cells(1000, 1))

works.

Cells was unqualified, so defaults to referring to cells on the active sheet or, if the code is in a sheet's code module, cells on that sheet.

 

[Dryver14]

try

Set rNodeIDs = shtNodeTemps.Cells(8, 1).resize(993)

 

[StevenHolden]

Big thanks for the quick replies guys.

I updated my code to the first of p45cal's suggestions and it works perfectly. I just assumed that the Cells would pick up the Sheet reference, wouldn't like to think how long it would have taken me to think of trying that...

Much appreciated!