goal seek using vba ?

[spurs]

I have a macro that calculates a function. There re 3 inputs to the function

Often I use the excel spreadsheet to do a goal seek to set the result of that faction by allowing one of the inputs to vary

Is there a simple way to program this in vba or do I basically have to write a complete reverse function from scratch?

 

[baitmaster]

Yes, and it's a very useful trick which I've used in some really powerful analytical tools

By way of example, I've entered the values 1, 2, 3 in cells A1:A3, then A4 formula "=sum(A1:A3)". The following code places the values 5, 6, 7 in cells B1:B3, being the changes required to each number in order to sum to 10

Option Explicit

Sub goalSeekExample()
    
Dim cl As Range, dblOriginal As Double
For Each cl In Range("A1:A3")
    
    dblOriginal = cl.Value                              ' store original value
    Range("A4").GoalSeek Goal:=10, ChangingCell:=cl     ' change each cell, one at a time, so A4 sum result = 10
    cl.Offset(0, 1) = cl.Value                          ' store goal seek result in adjacent cell
    cl.Value = dblOriginal                              ' reset value
    
Next cl


End Sub