CODEWITHSUNDEEP
excelvba
user1996971
user1996971

Reputation: 543

In VBA, is it possible to set a range to be relative to another range?

I'm writing a very long script, and I'm trying to find as many shortcuts as possible. I need to set up a lot of ranges before starting. Would it be possible to replace something like:

Dim Range1 As Range
Dim Range2 As Range 
Dim Range3 As Range
Dim i As Long

i = 2

Set Range1 = ("D" & i)
Set Range2 = ("H" & i)
Set Range3 = ("L" & i)

With some like 

Dim Range1 As Range
Dim Range2 As Range 
Dim Range3 As Range
Dim i As Long
Dim y As Long 

i = 2
y = 4

Set Range1 = ("D" & i)
Set Range2 = (Range1.Offset(,y))
Set Range3 = (Range2.Offset(,y))

It would be very helpful if I have 100 ranges to set, all equal distances from one another. If I were to use 100 ranges, would I need to Dim each one as I have here? Or can I do something like "Set RangeN as Range where N is every integer between 1 and 100?"

Thanks in advance.

Upvotes: 2

Views: 233

Answers (1)

Latch
Latch

Reputation: 368

You could use a dictionary.

Sub ex()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Dim i As Integer, y As Integer
Dim key
y = 4
For i = 0 To 2
    dict.Add i, Range("D2").Offset(, y * i)
Next
For Each key In dict
    dict(key).Value = "test" & key
Next
End Sub

dict(i) will be a range and you can do whatever you normaly would with it.

Upvotes: 2

Related Questions