Search code examples
excelvbaloopsnamed

VBA Excel goal seek loop over named range

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
Solution

  • 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