|
loop through ranges
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 |
loop through ranges
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("C5:D10") For Each varRecord In rngTemp.Rows For Each cell In varRecord.Cells MsgBox cell.Address Next Next If this post helps click Yes --------------- Jacob Skaria "David" wrote: 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 |
loop through ranges
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" wrote: 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("C5:D10") For Each varRecord In rngTemp.Rows For Each cell In varRecord.Cells MsgBox cell.Address Next Next If this post helps click Yes --------------- Jacob Skaria "David" wrote: 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 |
loop through ranges
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 --------------- Jacob Skaria "David" wrote: 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" wrote: 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("C5:D10") For Each varRecord In rngTemp.Rows For Each cell In varRecord.Cells MsgBox cell.Address Next Next If this post helps click Yes --------------- Jacob Skaria "David" wrote: 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 |
loop through ranges
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? "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "David" wrote: 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" wrote: 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("C5:D10") For Each varRecord In rngTemp.Rows For Each cell In varRecord.Cells MsgBox cell.Address Next Next If this post helps click Yes --------------- Jacob Skaria "David" wrote: 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 |
loop through ranges
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 "David" wrote: 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? "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "David" wrote: 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" wrote: 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("C5:D10") For Each varRecord In rngTemp.Rows For Each cell In varRecord.Cells MsgBox cell.Address Next Next If this post helps click Yes --------------- Jacob Skaria "David" wrote: 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 |
loop through ranges
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 "David" wrote: 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 "David" wrote: 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? "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "David" wrote: 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" wrote: 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("C5:D10") For Each varRecord In rngTemp.Rows For Each cell In varRecord.Cells MsgBox cell.Address Next Next If this post helps click Yes --------------- Jacob Skaria "David" wrote: 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 |
loop through ranges
NAmes should be within quotes..
Set rng1 = Range("rng1") Set rng2 = Range("rng2") Set rng3 = Range("rng3") If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: 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 "David" wrote: 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 "David" wrote: 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? "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "David" wrote: 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" wrote: 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("C5:D10") For Each varRecord In rngTemp.Rows For Each cell In varRecord.Cells MsgBox cell.Address Next Next If this post helps click Yes --------------- Jacob Skaria "David" wrote: 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 |
| All times are GMT +1. The time now is 08:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com