VBA: Set Range Variable

[ExcelKnut]

I'm trying to set a range variable but I keep getting an error (Run-time error '
1004': Method 'Range' of object'_worksheet' failed. Please help me to understand what I'm doing wrong.

Dim rng As Range
Set rng = Sheet24.Range(Cells(1, 1), Cells(41, 22))

Jeff

 

[steve the fish]

Try:

Dim rng As Range
Set rng = Sheet24.Range(Sheet24.Cells(1, 1), Sheet24.Cells(41, 22))

The cells part was using the active sheet not the sheet you wanted.

 

[MickG]

You need to qualify each part of the "Rng"address by adding the "." (dot) as shown, to reflect the sheet.

Dim rng As Range
With Sheets("Sheet24")
    Set rng = .Range(.Cells(1, 1), .Cells(41, 22))
End With
MsgBox rng.Address

 

[ExcelKnut]

Thanks guys! I tried to figure this out for two hours yesterday. I can't tell you how much I appreciate your assistance.

 

[MickG]

You're welcome