How do I create a For loop within a For loop?

Hi - I'm trying to set up a loop which first selects a range within a sheet
and then changes each of the cells within the selected range. There are
multiple ranges within the sheet that I need to do this for -- below is my
attempt at this (I get an error saying "For control varialbe already in
use"). Any advice is much appreciated!

For Each c In Worksheets("Aurora").Range("A1:Z1500").Cells
If c.Value = "MAX PRICE AFTER ADJUSTMENTS:" Then Set myRange =
Range(c.Value, Selection.End(xlDown)).Select & Range(Selection,
Selection.End(xlToRight)).Select
For Each c In Worksheets("Aurora").myRange
Set c.Value = c.Value - 0.5
Next

"Linking to specific cells in pivot table"
<[email protected]> wrote
in message
news:[email protected]...
>
> Hi - I'm trying to set up a loop which first selects a range within
> a sheet and then changes each of the cells within the selected
> range.
> There are multiple ranges within the sheet that I need to do this
> for -- below is my attempt at this (I get an error saying "For
> control varialbe already in use"). Any advice is much appreciated!
>
> For Each c In Worksheets("Aurora").Range("A1:Z1500").Cells
> If c.Value = "MAX PRICE AFTER ADJUSTMENTS:" Then Set myRange =
> Range(c.Value, Selection.End(xlDown)).Select & Range(Selection,
> Selection.End(xlToRight)).Select
> For Each c In Worksheets("Aurora").myRange
> Set c.Value = c.Value - 0.5
> Next
>

Hi,

Two things:

Use a different variable name in one of the loops (you have used 'c'
in both - change one to, say, 'd')

Also, you need a 'next' to go with each 'for'. Just add it on the end
I think from looking at your code.

HTH,

Alan.

I couldn't get your goal from the code supplied - I took a guess.

Need to see sample data to give you an accurate solution.


Pseudocode:
Loop through each cell in A1:Z1500
if this cell = "MAX PRICE AFTER ADJUSTMENTS:" then
subtract 0.5 from each value to the right of this cell
end if


Sub test()
Dim rng As Range, rngAdj As Range

For Each rng In Worksheets("Aurora").Range("A1:Z1500")
If rng.Value = "MAX PRICE AFTER ADJUSTMENTS:" Then
For Each rngAdj In Range(rng.Offset(0, 1), rng.End(xlToRight))
rngAdj.Value = rngAdj.Value - 0.5
Next
End If
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Linking to specific cells in pivot table"
<[email protected]> wrote in
message news:[email protected]...
> Hi - I'm trying to set up a loop which first selects a range within a
> sheet
> and then changes each of the cells within the selected range. There are
> multiple ranges within the sheet that I need to do this for -- below is my
> attempt at this (I get an error saying "For control varialbe already in
> use"). Any advice is much appreciated!
>
> For Each c In Worksheets("Aurora").Range("A1:Z1500").Cells
> If c.Value = "MAX PRICE AFTER ADJUSTMENTS:" Then Set myRange =
> Range(c.Value, Selection.End(xlDown)).Select & Range(Selection,
> Selection.End(xlToRight)).Select
> For Each c In Worksheets("Aurora").myRange
> Set c.Value = c.Value - 0.5
> Next

I couldn't get your goal from the code supplied - I took a guess.

Need to see sample data to give you an accurate solution.


Pseudocode:
Loop through each cell in A1:Z1500
if this cell = "MAX PRICE AFTER ADJUSTMENTS:" then
subtract 0.5 from each value to the right of this cell
end if


Sub test()
Dim rng As Range, rngAdj As Range

For Each rng In Worksheets("Aurora").Range("A1:Z1500")
If rng.Value = "MAX PRICE AFTER ADJUSTMENTS:" Then
For Each rngAdj In Range(rng.Offset(0, 1), rng.End(xlToRight))
rngAdj.Value = rngAdj.Value - 0.5
Next
End If
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Linking to specific cells in pivot table"
<[email protected]> wrote in
message news:[email protected]...
> Hi - I'm trying to set up a loop which first selects a range within a
> sheet
> and then changes each of the cells within the selected range. There are
> multiple ranges within the sheet that I need to do this for -- below is my
> attempt at this (I get an error saying "For control varialbe already in
> use"). Any advice is much appreciated!
>
> For Each c In Worksheets("Aurora").Range("A1:Z1500").Cells
> If c.Value = "MAX PRICE AFTER ADJUSTMENTS:" Then Set myRange =
> Range(c.Value, Selection.End(xlDown)).Select & Range(Selection,
> Selection.End(xlToRight)).Select
> For Each c In Worksheets("Aurora").myRange
> Set c.Value = c.Value - 0.5
> Next