Change range multiple times
 

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

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

Change range multiple times
 

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
-----Original Message-----
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 the
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 like
this:

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

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

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

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

Thanks
Jim


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

.

Change range multiple times
 

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

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

Change range multiple times
 

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)

"hotherps " wrote in message
...
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 the
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 like
this:

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

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

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

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

Thanks
Jim


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

Change range multiple times
 

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

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