Refer to range by variable string causes 1004 error

Question

I have a Sub with a number of ranges called rng1, rng2, rng3, etc.

I want to loop through all the ranges. I was hoping one of these methods of identifying the ranges would work, but they give me "Method 'Range' of object '_Global' failed":

Dim str as String
Dim I as Integer
Dim rng1 as Range, rng2 as Range, rng3 as Range

Set rng1 = Range("A10:A75")
Set rng2 = Range("A95:A175")
Set rng3 = Range("A180:A185")

For I = 1 to 3
   Range("rng1").Copy   'I didn't expect that to work
   Range("rng" & I).copy
   Range("rng" & CStr(I)).Copy
   str = "rng" & I
   Range(str).Copy
Next I

EDITED to clarify as per the comments. I know the code sample doesn't do anything useful, but it shows what I have tried.

Why would you not expect `Range("rng1")` to work but expect the others to work? Are these named ranges in Excel or variables in VBA somewhere else? — D Stanley, Mar 02 '20 at 20:57
Side note: you really want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. That link may also help you figure out the answer to your question. — cybernetic.nomad, Mar 02 '20 at 20:58
It looks like you may be trying to loop through a column using `i` as the row number which means "rng1" would need to be a Column letter *(something like `Range("A" & i)`)* although it's really unclear. You should [edit] your question to explain what you are trying to do because I can see the answer here going various ways. Really depends on what you need — urdearboy, Mar 02 '20 at 20:59
@cybernetic.nomad I'm only using select in this example, not in the actual code. — Shawn V. Wilson, Mar 02 '20 at 22:04
@DStanley I didn't expect it to work because I tried it last. These are variables in VBA (as the question now shows). — Shawn V. Wilson, Mar 02 '20 at 22:32

score 2 · Accepted Answer · answered Mar 03 '20 at 06:56

You can't refer to a variable by building its name as a string. That's not how variable work. You'll need an alternative.

Here's one

Dim rng(1 To 3) as Range

Set rng(1) = Range("A10:A75")
Set rng(2) = Range("A95:A175")
Set rng(3) = Range("A180:A185")

For I = 1 to 3
   rng(I).copy
   `...
Next I

Refer to range by variable string causes 1004 error

1 Answers1