Change range multiple times

  • Thread starter Thread starter hotherps
  • Start date Start date
H

hotherps

I'm using the following statement:

For Each Cell In Range("D3:J23")

I want the range in the For Next statement to move 8 colums to th
right and run again. Same row numbers. I need this to happen 20 times.


Then I want to increase each If Range value by 1 each time it loops
Same column numbers. This is also 20 times.To avoid writing out lik
this:

If Range("I29").Value >= Range("I26") And Cell.Value > "" An
Cells(Cell.Row, "AO") = "x" Then _
Cell.Value = Range("AO1")

If Range("I30").Value >= Range("I27") And Cell.Value > "" An
Cells(Cell.Row, "AO") = "x" Then _
Cell.Value = Range("AO1")

If Range("H29").Value >= Range("H26") And Cell.Value > "" An
Cells(Cell.Row, "AN") = "x" Then _
Cell.Value = Range("AN1")

If Range("H30").Value >= Range("H27") And Cell.Value > "" An
Cells(Cell.Row, "AN") = "x" Then _
Cell.Value = Range("AN1")
etc.

Thanks
Ji
 
Jim
You could try something like

dim i as integer
dim cl as range

for i = 0 to 7
for each cl in Range("D3:J23").offset(0,i).cells
'do your stuff
next cl
next i

I'm not totally sure what you are after but I think offset
may do what you want, there may be better ways

cheers
simon
 
Thanks Simon, but I could not get it to work. It seems like it might b
the right idea, but I'm not sure.

Ji
 
Something like?

For iCol = 4 To 164 Step 8
For Each Cell In Range(Cells(iCol,3),Cells(iCol+7,23)
If Cells(29, iCol+6).Value >= Cells(26, iCol+6) And _
Cell.Value > "" And _
Cells(Cell.Row, "AO") = "x" Then _
Cell.Value = Range("AO1")
'etc
Next Cell
Next iCol

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I'm probably not explaining myself well enough. Here is a piece of th
actual code:

For Each Cell In Range("D3:J23")

If Range("I29").Value >= Range("I26") And Cell.Value > "" An
Cells(Cell.Row, "AO") = "x" Then _
Cell.Value = Range("AO1")

If Range("H29").Value >= Range("H26") And Cell.Value > "" An
Cells(Cell.Row, "AN") = "x" Then _
Cell.Value = Range("AN1")

If Range("G29").Value >= Range("G26") And Cell.Value > "" An
Cells(Cell.Row, "AM") = "x" Then _
Cell.Value = Range("AM1")

If Range("J29").Value >= Range("J26") And Cell.Value > "" An
Cells(Cell.Row, "AP") = "x" Then _
Cell.Value = Range("AP1")

If Range("F29").Value >= Range("F26") And Cell.Value > "" An
Cells(Cell.Row, "AL") = "x" Then _
Cell.Value = Range("AL1")
Next

For Each Cell In Range("K3:Q23")

If Range("I30").Value >= Range("I27") And Cell.Value > "" An
Cells(Cell.Row, "AO") = "x" Then _
Cell.Value = Range("AO1")

If Range("H30").Value >= Range("H27") And Cell.Value > "" An
Cells(Cell.Row, "AN") = "x" Then _
Cell.Value = Range("AN1")

If Range("G30").Value >= Range("G27") And Cell.Value > "" An
Cells(Cell.Row, "AM") = "x" Then _
Cell.Value = Range("AM1")

If Range("J30").Value >= Range("J27") And Cell.Value > "" An
Cells(Cell.Row, "AP") = "x" Then _
Cell.Value = Range("AP1")

If Range("F30").Value >= Range("F27") And Cell.Value > "" An
Cells(Cell.Row, "AL") = "x" Then _
Cell.Value = Range("AL1")

I'm sure you can see how redundant it is. There are additional range
for both the "For Each" Statements and the "If Range" statments (abou
20 of each)

Thanks agai
 
Back
Top