iterate through a range of cells in a column for automating goal seek - macro

[Claudius]

Hi,
I am trying to write a simple macro to iterate through a range of cells in a column, that need to be computed as a goal seek function, with changing inputs from another cell in the coresponding row. I am a newbie in vba. Any help is greatly appreciated

Sub GoalSeek()

Range("G8").GoalSeek Goal:=100%, ChangingCell:=Range("D8")
End Sub

I would like to iterate from target cell G8 to the of the end of the column. The changing input is also from cell D8 to the end of the column
The formula is = G8 = 1 - H8(where H8 is dependant on changing cell D8)
As we go down; G9 = 1 - H9 (where H9 is dependant on changing cell D9)

Also how do i specify a percentage value in the goal, as it does not take a percent sign in the vba code.

Thanks!
Claudius

 

[shg]

Sub myGoalSeek()
    Dim lRow As Long
    Dim iRow As Long
    
    iRow = Cells(Rows.Count, "G").End(xlUp)
    
    For iRow = 8 To lRow
        If Cells(iRow, "G").HasFormula Then
            Cells(iRow, "G").GoalSeek Goal:=1, ChangingCell:=Cells(iRow, "D")
        End If
    Next iRow
End Sub

In VBA, % is the type declaration character for an Integer, so you can't use it in the intuitive way.

 

[Claudius]

Hi shg,
Thanks for the prompt response,
Its still not running correctly though.
I modified this line to change the irow to lrow, that might be a typo

But how does the lrow variable provide the count of rows from row number G8 to the last row with data, i dont comprehend..could you please explain?

iRow = Cells(Rows.Count, "G").End(xlUp)

Thanks,
Claudius

 

[shg]

That line had two errors, sorry:

[COLOR=red]l[/COLOR]Row = Cells(Rows.Count, "G").End(xlUp)[COLOR=red].Row[/COLOR]

It's the row you end up on if you select the last cell in col G and then press Ctrl+Up

 

[Claudius]

Thank you!

And I also learnt a new shortcut Ctrl up