excel - VBA Goalseek with Variable instead of Cell

Question

I need to use Goalseek in my VBA code, but rather than seeking a goal for a target "CELL" in my spreadsheet, I need to seek a goal for a "VARIABLE" within my VBA code.

e.g. Rather than setting some target CELL to "A3", I need to set a target VARIABLE declared in my VBA code.

Same applies, of course, to the "By changing" field.

Is that even possible?

Answer 1

Use a cell location that is off-screen to do the calculation, then once the process is finished, load the cell value into your variable, e.g.:

...GoalSeek ... ChangingCell:=Worksheets("Sheet1").Cells("A5000")
MyVar = Worksheets("Sheet1").Cells("A5000").Value