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"
crosoft.com wrote
in message
...

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"
crosoft.com wrote in
message ...
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