Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 74
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 107
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 74
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,942
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 74
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,942
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested Do Until Loop PJFry Excel Programming 4 September 20th 07 09:10 PM
Nested Loop JMay Excel Programming 4 December 18th 06 12:19 AM
simple loop question Rob Excel Programming 5 September 13th 05 08:10 PM
Simple question on For...Next loop Alex Excel Programming 1 July 26th 05 03:30 PM
Nested Loop Frank Kabel Excel Programming 6 September 12th 04 02:41 AM


All times are GMT +1. The time now is 01:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"