Loop through variables which defined as range

blume77

New Member
Joined
Aug 26, 2019
Messages
1
Hello,
how can I loop through ranges. What I have already tried so far.
Is it possible?

Dim Variabel as Range
Dim Variabel_1 as Range
Dim Variabel_2 as Range
Dim Variabel_3 as Range
Dim I as Long

' the ranges are as an example
Set Variabel_1 = Range("A1")
Set Variabel_2 = Range("B5")
Set Variabel_3 = Range("C10")

For I = 1 to 3
Set Variabel = Range("Variabel_" & I) ' What is wrong with at this part?
next I
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,816
Office Version
2019
Platform
Windows
Hi, welcome to forum

Yes its possible -

If variables have same name the you could use an array

couple of ideas maybe

Code:
Sub Idea1()
    Dim Variable(1 To 3) As Range
    Dim i As Integer
    
    For i = 1 To 3
        Set Variable(i) = Range(Choose(i, "A1", "B5", "C10"))
        MsgBox Variable(i).Address
    Next i
End Sub

or

Code:
Sub Idea2()
    Dim Variable(1 To 3) As Range, rng As Range
    Dim i As Integer
    
    For Each rng In Range("A1, B5, C10")
        i = i + 1
        Set Variable(i) = rng
        MsgBox Variable(i).Address
    Next rng
End Sub
Others here may have alternative suggestions


Hope helpful


Dave
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
Another way
Code:
Sub blume77()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("A1", "B5", "C10")
   For i = 0 To UBound(Ary)
      Range(Ary(i)).Interior.ColorIndex = i + 3
   Next i
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,142
Messages
5,466,924
Members
406,510
Latest member
wizekor

This Week's Hot Topics

Top