Relative Reference - Formula to Goal Seek Using Cell on Different Weooksheet

[usermrex]

I am using Excel 2010. I want to use a relative reference to goal seek using a "ChangingCell" that is in a different worksheet...

ActiveCell.GoalSeek Goal:=0, ChangingCell:=Sheets("Volumes").Range("O11")

... but I am getting an error message that the reference is not valid. What should I call the ChangingCell on the "Volumes" sheet so that it relates to the ActiveCell (which is Q23 on a sheet called "Results")?

Also, I would like the ChangingCell to also be relative in that it moves down rows as my original "Active Cell" moves down?

ie ActiveCell is "Results Q23" and ChangingCell is "Volumes O11", so if the ActiveCell is "Results Q24", the ChangingCell needs to be "Volumes O12".

Is this even possible? Thanks very much.

 

[JoeMo]

Try this - you can adjust the constants to suit your sheet, but you must run this from the Results sheet with the cell you want to goal seek on active.

Sub GoalSeekDynamic()
'run with Results sheet active
Const R As Long = 11 'start row on Volumes sheet
Const B As Long = 23 'start row on results sheet
Dim rw As Long, col As Long
rw = ActiveCell.Row
col = ActiveCell.Column
Cells(rw, col).GoalSeek goal:=0, changingcell:=Sheets("Volumes").Cells(R + rw - B, "O")
End Sub

 

[usermrex]

Thanks Joe! Very fast AND it works!!

 

[JoeMo]

Thanks Joe! Very fast AND it works!!

You are welcome. Thanks for the reply.