Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Faster way to loop through two ranges | Excel Programming | |||
named ranges in a For Loop | Excel Programming | |||
loop through all named ranges | Excel Programming | |||
Loop through ranges | Excel Programming | |||
loop to name ranges | Excel Programming |