loop through ranges

[David]

I'm suffering from brain freeeze at the moment...

Assuming i've already used Dim and Set, i have something like:

For Each cl in myRng1
Do something dependent on this range
Next cl
For Each cl in myRng2
Do something dependent on this range
Next cl

How could i loop through ranges something like:
for i = 1 to 2
for each cl in (what to use here?)
Do something (function of i)
Next cl
Next i

I can sort the (function of i) bit, it's looping through range names where
i'm stuck
Thanks

 

[Jacob Skaria]

Try the below. If this does not help post back with an example..and explain
what you are expecting..

Dim rngTemp As Range

Set rngTemp = Range("C510")

For Each varRecord In rngTemp.Rows
For Each cell In varRecord.Cells
MsgBox cell.Address
Next
Next



If this post helps click Yes

 

[David]

The code below works fine but i'm guessing there will be a way to use 1 loop
instead of 3. If, say, i could have range names and symbol character names in
a 3 x 2 array and loop through the array.. ?? (i've tried and failed)

Sub PopulateTimeChart()
Dim TimeChart As Range ' grid populated with symbols to show timing of
various events
Dim RefurbDate As Range ' column range populated w/ years: 2012, 2020, etc
Dim ReplaceDate As Range ' ditto
Dim EnhanceDate As Range 'ditto
Dim EndDates As Range 'ditto
Dim DateHeaders As Range 'Timechart column headers populated with years:
2009, 2101, etc (sequentially, step 1 year)
Dim cl_1 As Range, cl_2 As Range, cl_3 As Range
Dim YearsRemaining As Integer, Period As Integer, i As Integer

Application.ScreenUpdating = False

Set TimeChart = Range("TimeChart")
Set RefurbDate = Range("RefurbDate")
Set ReplaceDate = Range("ReplaceDate")
Set EnhanceDate = Range("EnhanceDate")
Set EndDates = Range("EndDate")
Set DateHeaders = TimeChart.Offset(-1).Resize(1)

TimeChart.ClearContents

For Each cl_1 In EnhanceDate
If Not Val(cl_1) = 0 Then
For Each cl_2 In DateHeaders
If cl_2 = cl_1 Then
Set cl_3 = Intersect(cl_1.EntireRow, cl_2.EntireColumn)
Period = Val(cl_1.Offset(, 1)) 'may be zero
YearsRemaining = Intersect(cl_1.EntireRow, EndDates) -
cl_2
If Period = 0 Then
cl_3.Value = "u" 'diamond symbol
Else
For i = 0 To YearsRemaining Step Period
cl_3.Offset(, i).Value = "u" 'diamond symbol
Next i
End If
End If
Next cl_2
End If
Next cl_1

For Each cl_1 In RefurbDate
If Not Val(cl_1) = 0 Then
For Each cl_2 In DateHeaders
If cl_2 = cl_1 Then
Set cl_3 = Intersect(cl_1.EntireRow, cl_2.EntireColumn)
Period = Val(cl_1.Offset(, 1)) 'may be zero
YearsRemaining = Intersect(cl_1.EntireRow, EndDates) -
cl_2
If Period = 0 Then
cl_3.Value = "¬" 'star symbol
Else
For i = 0 To YearsRemaining Step Period
cl_3.Offset(, i).Value = "¬" 'star symbol
Next i
End If
End If
Next cl_2
End If
Next cl_1

For Each cl_1 In ReplaceDate
If Not Val(cl_1) = 0 Then
For Each cl_2 In DateHeaders
If cl_2 = cl_1 Then
Set cl_3 = Intersect(cl_1.EntireRow, cl_2.EntireColumn)
Period = Val(cl_1.Offset(, 1)) 'may be zero
YearsRemaining = Intersect(cl_1.EntireRow, EndDates) -
cl_2
If Period = 0 Then
cl_3.Value = "l" 'bullet symbol
Else
For i = 0 To YearsRemaining Step Period
cl_3.Offset(, i).Value = "l" 'bullet symbol
Next i
End If
End If
Next cl_2
End If
Next cl_1
End Sub

 

[Jacob Skaria]

You just need one loop...You can get the values as below.

Dim varRec As Variant
Dim rng1 As Range, rng2 As Range, rng3 As Range

Set rng1 = Range("C5:C10")
Set rng2 = Range("E5:E10")
Set rng3 = Range("G5:G10")

For lngRow = 1 To rng1.Rows.Count
MsgBox rng1.Cells(lngRow, 1)
MsgBox rng2.Cells(lngRow, 1)
MsgBox rng3.Cells(lngRow, 1)
Next

If this post helps click Yes

 

[David]

Jacob,
Thanks once more
One more try (sorry if I am failing to articulate the problem effectively)
Mapping your example over to my code...
Each of the 3 lines in your For Next loop represents a process in my case
See my code, each process is similar.
The 3 processes could be condensed into one if I could use an outer loop
to loop through the 3 range references and the 3 process differentiators
(symbols in my case) - i can get the symbols from an array
How do i deal with the range references?

 

[David]

The penny has dropped...
no problem with a rangge name text reference (code below)
but what if i wanted to use a variable declared as a range instead?

Sub LoopRngNames()
Dim arr1(1 To 3), arr2(1 To 3)
Dim i As Integer, cl As Range

arr1(1) = "rng1": arr1(2) = "rng2": arr1(3) = "rng3"
arr2(1) = "cat": arr2(2) = "bat": arr2(3) = "dog"

For i = 1 To 3
For Each cl In Range(arr1(i))
cl.Select
MsgBox arr2(i) 'process
Next
Next i
End Sub

 

[Jacob Skaria]

Hi David

In the below code try using names as rng1,rng2 and rng3 instead of the
ranges mentioned earlier.

Dim varRec As Variant
Dim rng1 As Range, rng2 As Range, rng3 As Range

Set rng1 = Range(rng1)
Set rng2 = Range(rng2)
Set rng3 = Range(rng3)

For lngRow = 1 To rng1.Rows.Count
MsgBox rng1.Cells(lngRow, 1)
MsgBox rng2.Cells(lngRow, 1)
MsgBox rng3.Cells(lngRow, 1)
Next


If this post helps click Yes

 

[Jacob Skaria]

NAmes should be within quotes..

Set rng1 = Range("rng1")
Set rng2 = Range("rng2")
Set rng3 = Range("rng3")


If this post helps click Yes