changing range in a loop

[gordsky]

Hi,

I have a number of ranges but for the purpose of this question will limit to 3. I have a for loop that does some things with Rng1 which I would like to repeat over the remaining ranges. I dont want to join the ranges or use a union due to the code later on in the macro. Is there a way to increment the Rng by 1 on each loop something along the lines of

Set Rng1 = Sheets(Sheet1).Range("E11:E43")
Set Rng2 = Sheets(Sheet1).Range("E55:E83")
Set Rng3 = Sheets(Sheet1).Range("K11:K43")

for i = 1 to 3

with Rng(i)

'do something

end with
next i

any help or suggestions would be appreciated

 

[hajiali]

Try:

With Rng & i

 

[gordsky]

Hi and thanks for the reply.
I did try that earlier but couldn't get it to work. As a test I just tried colouring the cells in each range but kept getting either object required error or out of script. Could you maybe expand on your suggestion

 

[hajiali]

Could you copy your code over I will test it out? I was will see if it works I did not test the above but if you post a copy of the code I can test It out on my end

 

[gordsky]

Sub test()
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range
Dim i As Long

Set Rng1 = Sheets(Sheet1).Range("E11:E43")
Set Rng2 = Sheets(Sheet1).Range("E55:E83")
Set Rng3 = Sheets(Sheet1).Range("K11:K43")

For i = 1 To 3

With Rng & i

.Interior.Color = RGB(255, 255, 0)

End With

Next i

End Sub

 

[hajiali]

I also could Not figure it out. However I would suggest adding another Dim as range see example below:

Sub test()
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, AllRng As Range
Dim i As Long
Dim Rng As Variant

Set Rng1 = Sheets("Sheet2").Range("E11:E43")
Set Rng2 = Sheets("Sheet2").Range("E55:E83")
Set Rng3 = Sheets("Sheet2").Range("K11:K43")
Set AllRng = Sheets("Sheet2").Range("E11:E43, E55:E83, K11:K43")
    With AllRng

        .Interior.Color = RGB(255, 255, 0)

    End With

End Sub

 

[johnnyL]

Sub ColorRanges()
'
    Dim Ranges As Range
'
    Set Ranges = Sheets("Sheet2").Range("E11:E43, E55:E83, K11:K43")
'
    With Ranges
        .Interior.Color = RGB(255, 255, 0)
    End With
End Sub

 

[Alex Blakenburg]

Would something like this work for you ?

Sub test()

    Dim arrRng(1 To 3) As Range
    Dim sht As Worksheet
    Dim i As Long
    
    Set sht = Worksheets("Sheet1")
    
    With sht
        Set arrRng(1) = .Range("E11:E43")
        Set arrRng(2) = .Range("E55:E83")
        Set arrRng(3) = .Range("K11:K43")
    End With
    
    For i = 1 To UBound(arrRng)
        arrRng(i).Interior.Color = RGB(255, 255, 0)
    Next i

End Sub

 

[mohadin]

Hi
what about

Sub test()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Set rng1 = Range("E11:E43")
    Set rng2 = Range("E55:E83")
    Set rng3 = Range("K11:K43")
    a = Array(rng1, rng2, rng3)
    For i = 1 To 3
        a(i - 1).Select
    Next i
End Sub

 

[gordsky]

thanks all, went with the option suggested by Alex only for the reason that it doesn't require joining the ranges first