loop through columns
 

I have some code that runs through the following range:

G9:CX300

I would like to loop through 8 columns at a time instead of the whol
range. i.e.

G9:N300
O9:V300

etc

Thank

--
Message posted from http://www.ExcelForum.com

loop through columns
 

Try this, it sets a range irng in blocks of 8 columns

Sub StepLoop()
Dim irng As Range
Dim iloop As Integer
For iloop = 7 To 102 Step 8
Set irng = Range(Cells(9, iloop), Cells(300, iloop + 7))
' act on your range irng in here
Next iloop
End Sub

Cheers
Nigel

"hotherps " wrote in message
...
I have some code that runs through the following range:

G9:CX300

I would like to loop through 8 columns at a time instead of the whole
range. i.e.

G9:N300
O9:V300

etc

Thanks


---
Message posted from http://www.ExcelForum.com/

loop through columns
 

Hi
and what are you trying to do with these ranges?. You may
post the relevant part of your existing code

-----Original Message-----
I have some code that runs through the following range:

G9:CX300

I would like to loop through 8 columns at a time instead
of the whole
range. i.e.

G9:N300
O9:V300

etc

Thanks


---
Message posted from http://www.ExcelForum.com/

.

loop through columns
 

Thanks Guys, here is the code:

Dim c 'don't know as what
Dim i As Integer
Dim rng As Range

On Error Resume Next
With Sheets("MonTest")
.Range("G11:CX125").ClearContents
End With
Range("G11:CX125").Formula
"=IF(AND(g$9=$B11,g$9<=$C11),""..."","""")"
Range("G11:CX125").Value = Range("G11:CX125").Value


For Each Cell In Range("G11:CX125")
c = Array(7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
For i = 0 To 11
If Cells(356, c(i)).Value = Cells(305, c(i)) And Cell.Value "" _
And Cells(Cell.Row, c(i) + 97) = "x" Then _
Cell.Value = Cells(9, c(i) + 97)
Next i
Next


What it should do (not currently working) is to determine if 356 30
and + 97 = "x", and the cell value = ... If so place the the text tha
is in the corresponding cell. (One cell above)

Do this 8 columns at a time from D11 to CX125

Thanks
Ji

--
Message posted from http://www.ExcelForum.com

loop through columns
 

Nigel,

Is this you way you suggested to set it up?

Dim c
Dim i As Integer
Dim rng As Range
Dim irng As Range
Dim iloop As Integer

On Error Resume Next
With Sheets("MonTest")
.Range("G11:CX125").ClearContents
End With
Range("G11:CX125").Formula
"=IF(AND(g$9=$B11,g$9<=$C11),""..."","""")"
Range("G11:CX125").Value = Range("G11:CX125").Value

'For Each Cell In Range("G11:CX125")

For iloop = 7 To 102 Step 8
Set irng = Range(Cells(9, iloop), Cells(300, iloop + 7))
' act on your range irng in here

c = Array(20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10)
For i = 0 To 11
If Cells(356, c(i)).Value = Cells(305, c(i)) And Cell.Value "" _
And Cells(Cell.Row, c(i) + 97) = "x" Then _
Cell.Value = Cells(9, c(i) + 97)
Next i
'Next
Next iloop

Does not seem to work
thank

--
Message posted from http://www.ExcelForum.com

loop through columns
 

"Does not seem to work"???? What happens?

Alan Beban

hotherps < wrote:
Nigel,

Is this you way you suggested to set it up?

Dim c
Dim i As Integer
Dim rng As Range
Dim irng As Range
Dim iloop As Integer

On Error Resume Next
With Sheets("MonTest")
Range("G11:CX125").ClearContents
End With
Range("G11:CX125").Formula =
"=IF(AND(g$9=$B11,g$9<=$C11),""..."","""")"
Range("G11:CX125").Value = Range("G11:CX125").Value

'For Each Cell In Range("G11:CX125")

For iloop = 7 To 102 Step 8
Set irng = Range(Cells(9, iloop), Cells(300, iloop + 7))
' act on your range irng in here

c = Array(20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10)
For i = 0 To 11
If Cells(356, c(i)).Value = Cells(305, c(i)) And Cell.Value "" _
And Cells(Cell.Row, c(i) + 97) = "x" Then _
Cell.Value = Cells(9, c(i) + 97)
Next i
'Next
Next iloop

Does not seem to work
thanks


---
Message posted from http://www.ExcelForum.com/

loop through columns
 

The code only runs in the original for next range, i needed it to ru
again on the next 8 columns to the right, and so on. So it would b
like:

For Each cell In Range("G11:N125")
then O11:V125
AD11 etc

--
Message posted from http://www.ExcelForum.com