VBA Excel goal seek loop over named range

Question

Im trying to create a simple goal seek macro

I have a named range "Calc_Output" which selects cells F5:M5 I have a named range "Calc_Input" which selects cells F6:M6

Now I want a loop to have a loop that goal seekscell F6s input so F5s output is 0. And then to rerun the goal seek so it does the same for cell G6 and F6 etc. Until all outputs are 0.

I currently have the following:

Dim k As Integer

 For k = 1 To 8

Range("Calc_Output").Cells(1, k).GoalSeek Goal:=0, ChangingCell:=Range("Calc_Input").Cells(1, k)

 Next k

Chris Chris · Accepted Answer · 2020-05-15T17:49:50

try this

Sub GoalSeekRange()

Dim rng1 As Range, rng2 As Range
Dim i as long

Set rng1 = Range("Calc_Input")
Set rng2 = Range("Calc_Output")

    For i = 1 To rng1.Count
        rng2.Cells(1, i).GoalSeek Goal:=0, ChangingCell:=rng1.Cells(1, i)
    Next
    
End Sub

VBA Excel goal seek loop over named range

1 Answers