Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple nested loop question
I have a range that is a single row A1..Z1. I need to loop through this
range interrogating every cell in this range and if true, then write a number in the corresponding cell in a similarly sized array (A3..Z3). I think I know how to do this, but then I need to move the destination down one row (A4..Z4) and repeat. I need to drop down a row 18 times. So I need a nested loop, but I don't know how to increment the destination in the outer loop. noob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple nested loop question
for i = 1 to whatever step 18
do your thing next i -- Don Guillett Microsoft MVP Excel SalesAid Software "fedude" wrote in message ... I have a range that is a single row A1..Z1. I need to loop through this range interrogating every cell in this range and if true, then write a number in the corresponding cell in a similarly sized array (A3..Z3). I think I know how to do this, but then I need to move the destination down one row (A4..Z4) and repeat. I need to drop down a row 18 times. So I need a nested loop, but I don't know how to increment the destination in the outer loop. noob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple nested loop question
hi
see if this helps dim rang as range set rang = activesheet.range('put your range here') for each cell in rng do whatever u want to do with cell next -- hemu "fedude" wrote: I have a range that is a single row A1..Z1. I need to loop through this range interrogating every cell in this range and if true, then write a number in the corresponding cell in a similarly sized array (A3..Z3). I think I know how to do this, but then I need to move the destination down one row (A4..Z4) and repeat. I need to drop down a row 18 times. So I need a nested loop, but I don't know how to increment the destination in the outer loop. noob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple nested loop question
I was hoping to have the loop move me down a row.
"Don Guillett" wrote: for i = 1 to whatever step 18 do your thing next i -- Don Guillett Microsoft MVP Excel SalesAid Software "fedude" wrote in message ... I have a range that is a single row A1..Z1. I need to loop through this range interrogating every cell in this range and if true, then write a number in the corresponding cell in a similarly sized array (A3..Z3). I think I know how to do this, but then I need to move the destination down one row (A4..Z4) and repeat. I need to drop down a row 18 times. So I need a nested loop, but I don't know how to increment the destination in the outer loop. noob |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple nested loop question
Here is my single row loop. I need a way to increment rDest to
A4..A5....A21 and run this loop each time: Public Sub writeRange() Dim rDest As Range Dim i As Long With Range("A1").Resize(1, 26) Set rDest = Range("A3").Resize(.Rows.Count, .Columns.Count) For i = 1 To .Count If IsNumeric(.Cells(i).Value) Then If .Cells(i).Value = 1 Then rDest(i) = Cells(i).Value End If End If Next i End With End Sub "Hemant_india" wrote: hi see if this helps dim rang as range set rang = activesheet.range('put your range here') for each cell in rng do whatever u want to do with cell next -- hemu "fedude" wrote: I have a range that is a single row A1..Z1. I need to loop through this range interrogating every cell in this range and if true, then write a number in the corresponding cell in a similarly sized array (A3..Z3). I think I know how to do this, but then I need to move the destination down one row (A4..Z4) and repeat. I need to drop down a row 18 times. So I need a nested loop, but I don't know how to increment the destination in the outer loop. noob |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple nested loop question
hi
here is some do nothing code that illistrates how a nested loop works. maybe you can use it as an example Sub coloritup() Dim r As Range Set r = Range("A1") 'start row For n = 1 To 7 For Each cell In Range(r, r.Offset(0, 3)) cell.Interior.ColorIndex = 6 Next cell Set r = r.Offset(1, 0) 'drop down 1 row Next n End Sub regards FSt1 "fedude" wrote: I was hoping to have the loop move me down a row. "Don Guillett" wrote: for i = 1 to whatever step 18 do your thing next i -- Don Guillett Microsoft MVP Excel SalesAid Software "fedude" wrote in message ... I have a range that is a single row A1..Z1. I need to loop through this range interrogating every cell in this range and if true, then write a number in the corresponding cell in a similarly sized array (A3..Z3). I think I know how to do this, but then I need to move the destination down one row (A4..Z4) and repeat. I need to drop down a row 18 times. So I need a nested loop, but I don't know how to increment the destination in the outer loop. noob |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple nested loop question
Thank you. You gave me the code to increment through rows.
I'm sure this could be done better, but I did get it working. (Limited knowledge is dangerous) Here is my code. Suggestions for improvement welcome.... Dim rDest As Range Dim i As Long Dim n As Long With Range('A1').Resize(1, 26) Set rDest = Range('A3').Resize(.Rows.Count, .Columns.Count) ' start row For n = 1 To 11 For i = 1 To .Count If IsNumeric(.Cells(i).Value) Then If .Cells(i).Value = 1 Then rDest(i) = .Cells(i).Value End If End If Next i Set rDest = rDest.Offset(1, 0) 'drop down 1 row Next n End With "FSt1" wrote: hi here is some do nothing code that illistrates how a nested loop works. maybe you can use it as an example Sub coloritup() Dim r As Range Set r = Range("A1") 'start row For n = 1 To 7 For Each cell In Range(r, r.Offset(0, 3)) cell.Interior.ColorIndex = 6 Next cell Set r = r.Offset(1, 0) 'drop down 1 row Next n End Sub regards FSt1 "fedude" wrote: I was hoping to have the loop move me down a row. "Don Guillett" wrote: for i = 1 to whatever step 18 do your thing next i -- Don Guillett Microsoft MVP Excel SalesAid Software "fedude" wrote in message ... I have a range that is a single row A1..Z1. I need to loop through this range interrogating every cell in this range and if true, then write a number in the corresponding cell in a similarly sized array (A3..Z3). I think I know how to do this, but then I need to move the destination down one row (A4..Z4) and repeat. I need to drop down a row 18 times. So I need a nested loop, but I don't know how to increment the destination in the outer loop. noob |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
simple nested loop question
hi
looks good to me. simple..to the point. what is really important is work/don't work. if it works then its ok. there may be simpler ways to do it but i like the way it's layed out. easy to read and follow the logic. Regards FSt1 "fedude" wrote: Thank you. You gave me the code to increment through rows. I'm sure this could be done better, but I did get it working. (Limited knowledge is dangerous) Here is my code. Suggestions for improvement welcome.... Dim rDest As Range Dim i As Long Dim n As Long With Range('A1').Resize(1, 26) Set rDest = Range('A3').Resize(.Rows.Count, .Columns.Count) ' start row For n = 1 To 11 For i = 1 To .Count If IsNumeric(.Cells(i).Value) Then If .Cells(i).Value = 1 Then rDest(i) = .Cells(i).Value End If End If Next i Set rDest = rDest.Offset(1, 0) 'drop down 1 row Next n End With "FSt1" wrote: hi here is some do nothing code that illistrates how a nested loop works. maybe you can use it as an example Sub coloritup() Dim r As Range Set r = Range("A1") 'start row For n = 1 To 7 For Each cell In Range(r, r.Offset(0, 3)) cell.Interior.ColorIndex = 6 Next cell Set r = r.Offset(1, 0) 'drop down 1 row Next n End Sub regards FSt1 "fedude" wrote: I was hoping to have the loop move me down a row. "Don Guillett" wrote: for i = 1 to whatever step 18 do your thing next i -- Don Guillett Microsoft MVP Excel SalesAid Software "fedude" wrote in message ... I have a range that is a single row A1..Z1. I need to loop through this range interrogating every cell in this range and if true, then write a number in the corresponding cell in a similarly sized array (A3..Z3). I think I know how to do this, but then I need to move the destination down one row (A4..Z4) and repeat. I need to drop down a row 18 times. So I need a nested loop, but I don't know how to increment the destination in the outer loop. noob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested Do Until Loop | Excel Programming | |||
Nested Loop | Excel Programming | |||
simple loop question | Excel Programming | |||
Simple question on For...Next loop | Excel Programming | |||
Nested Loop | Excel Programming |