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

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

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

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

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

simple nested loop question
 

You weren't very clear and I guess I misunderstood

for i = 1 to 18
if cells(i,"a")="noob" then do whatever
next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fedude" wrote in message
...
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

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

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

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