1

I have two range statements:

Set s1 = Sheets("Key").Range("A7:A28")
Set s1 = Sheets("Key").Range(Cells(7, 1), Cells(28, 1)

The first statement works whether the sheet "Key" is active or not. The second statement only works when "Key" is active

The second statement results in a run-time error '1004': Application defined or object defined error

What is the correct way of setting a range using row/column syntax?

Community
  • 1
  • 1
C0ppert0p
  • 634
  • 2
  • 7
  • 23

1 Answers1

0

Declare your worksheet, and then use that with your range Cells() objects:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Key")

With ws
    Set s1 = .Range("A7:A28")
    Set s1 = .Range(.Cells(7, 1), .Cells(28, 1))
end with

Or if you prefer not to declare your worksheet, this would work too:

With ThisWorkbook.Worksheets("Key")
    Set s1 = .Range("A7:A28")
    Set s1 = .Range(.Cells(7, 1), .Cells(28, 1))
end with

Notice that I changed your Cells() (which is referring to the ActiveSheet), to .Cells() (which is now referring to your With Statement).

K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43