Goal Seek dynamic row referencing

[jaustin]

I have a several hundred row table running in Excel 2016 365Pro Plus. I’m trying to implement GoalSeek to determine a residual value on a row by row basis containing propertytransactions. I’ve been successfulrunning the following code on a single row (row 5):
Sheets(“Residual_Analysis”).Select
Range(“AH”& Trim$(Str$(iCountResidual).Select ‘ iCountResidual is my row counter
ActiveCell.Value= 100 ‘ initial value for the ChangingCell
Range(“AG”& Trim$(Str$(iCountResidual).Select ‘Cell for formula
ActiveCell.Formula= “=MS-(V5*AH5)” ‘ M5contains the base value, V5 contains a formula variable
Range(“AG5”).GoalSeek Goal:=0, ChangingCell:=Range(“AH5”)

I want to replace “5” in the above code with iCountResidual(value is 5 in the example). I’ve triedseveral configurations.
The closest I’ve gotten is:
Residual_Cell= Range(“AG” & Trim$(Str$(iCountResidual))).Value = 0
Changing_Cell= Range(“Ai” & Trim$(Str$(iCountResidual)))
Range(“Residual_Cell”).GoalSeek:=0,ChangingCell:=(“Changing_Cell”)

I receiveRun-time error ‘1004’:
Method’Range’of object’_Global failed

Thanks inadvance,
J.Austin

 

[fpritt24]

I assume you already have iCountResidual declared in your scope, but why not implement a for loop to go through the hundreds of rows you have?

Sub macro()
Dim iCountResidual As Integer
For iCountResidual = 5 To 1000
    Range("AH" & iCountResidual).Select
    ActiveCell.Value = 100
    Range("AG" & iCountResidual).Select
    ActiveCell.Formula = "=M" & iCountResidual & "-(V" & iCountResidual & "*AH" & iCountResidual & ")"
    Range("AG" & iCountResidual).GoalSeek Goal:=0, ChangingCell:=Range("AH" & iCountResidual)
Next iCountResidual
End Sub

 

[jaustin]

fpritt24,

Thanks! The code worked perfectly. I think my issue was how I was defining the formula. For now, this is an amazing feature of Excel that I hadn't used previously and it should save me a lot of time and extra code.

Thanks again,

J.Austin