Loop through rows completing tasks in each row conditionally

[Randy1234]

I need a little assistance with a code that I am writing for one of my worksheets. Here is what I am trying to do:

1. loop through each row 1-200
2. If the cell in column "Z" is > 0 then
3. Goalseek the cell in column "AS" of the same row to 0.25 by changing the cell in column "X" of the same row.

note: each row is looked at independently.

Here is the code I have started, but I keep getting an error on the goalseek line.

error: "Method 'Range' of Object'_Global' Failed

Thanks for the assistance.

Randy

Sub VC_Calc()

Dim Price As Range
Dim Cell As Range

Set Price = Range("Z1:Z200")

For Each Cell In Price
With Cell
If Cells(.Row, "Z").Value > 0 Then
Range(.Row, "AS").GoalSeek Goal:=0.25, ChangingCell:=Range(.Row, "X")
End If
End With
Next Cell

End Sub

 

[Zathras]

This isn't tested, but you could try either
[tt]
Cells(.Row, "AS").GoalSeek Goal:=0.25, ChangingCell:=Cells(.Row, "X")
[/tt]
or
[tt]
Range("AS" & .Row).GoalSeek Goal:=0.25, ChangingCell:=Range("X" & .Row)
[/tt]

 

[Randy1234]

Thanks, but neither fix worked. The first fix I had already tried and received the same error. The second fix gave me a new error: "Reference is not valid".

 

[BenRowe]

Maybe it has something to do with the values being used for the GoalSeek. Can you manually complete GoalSeek on a single row?

 

[WalterContrata]

randy1234,

I tried modifying your code according to Zathras's post, and it worked on my machine (Windows 2000, Office 2000).

For convenience, I changed your columns to "A", "B", and "C". In column "A", I put the function "= RC2^2".

Sub VC_Calc()

Dim Price As Range
Dim Cell As Range

Set Price = Range("C1:C200")

For Each Cell In Price
    With Cell
        If Cells(.Row, "C").Value > 0 Then
            Cells(.Row, "A").GoalSeek Goal:=0.25, _
                ChangingCell:=Cells(.Row, "B")
        End If
    End With
Next Cell

End Sub

Never used "GoalSeek" before. Nice method.

Best Regards,
Walter

 

[Randy1234]

Got it...thanks to all. I just realized my initial range included the field headers and that was giving me the error. Once moved the start of the range from Z1 to Z2 everything worked out great!!