Macro for using Goal Seek over a range of cells

Question

I'm trying to automate the GoalSeek function over a range of cells. Each cell in Columns I and J calculates a quantity of interest using two different methods, and the results must agree.

Each cell in Column K is the difference between the corresponding cells in Columns I and J. Each cell in Column F contains the value of the parameter that, when GoalSeek converges, drives K to zero (that is, I = J). Here is the code I'm using:

Sub TargetTemp()
    
    Do Until Range("K26:K119") = 0
        
        Range("J26:J119").GoalSeek Goal:=Range("I26:I119"), ChangingCell:=Range("F26:F119")
        
    Loop
    
End Sub

When I run it I get type mismatch error 13, can't figure out why. Can anyone help?

Answer 1

You should use GoalSeek for individual cells, not for a whole range at once. Accuracy of calculations you can control by changing Application.MaxChange property in relation to your data. The default value is 0.001. Something like this:

    Sub TargetTemp()
        Dim i As Long
        Application.MaxChange = 0.0001
        For i = 1 To Range("J26:J119").Count
            Range("J26:J119")(i).GoalSeek Goal:=Range("I26:I119")(i), _
                ChangingCell:=Range("F26:F119")(i)
        Next i
    End Sub