text: Q:
  
  GoalSeek inside loop - Run time error 1004 : Reference is not valid
  
  I'm writing a code which goes into different sheets and performs GoalSeek on rows that have the word "Obj" and "Var". After completing my first sheet, the code moves on to the second sheet and prompted the error 1004. It says my reference for the Do ... Loop function is no longer valid. Why could that be?
  Sub GoalSeek()  
  
  Dim FirstAddress As String
  Dim SecondAddress As String
  Dim Arr As Variant
  Dim Rng As Range
  Dim Rng2 As Range
  Dim y As Long
  Dim i As Long
  
  Arr = Array("SheetA", "SheetB")
  
  For i = LBound(Arr) To UBound(Arr)
  
      With Worksheets(Arr(i)).Range("A1:BZ500")
  
      Set Rng = .Find("Obj", LookIn:=xlValues)
  
      If Not Rng Is Nothing Then
  
          FirstAddress = Rng.Address
  
          Set Rng2 = .Find("Var", LookIn:=xlValues)
  
          If Not Rng2 Is Nothing Then
  
              SecondAddress = Rng2.Address
  
              'Error Occurred Here
              Do
  
                  Rng.Offset(0, y + 1).GoalSeek Goal:=0, ChangingCell:=Rng2.Offset(0, y + 1)
  
                  y = y + 1
  
              Loop Until y = 12
  
          End If
  
      End If
  
      End With
  
  Next i
  
  End Sub
  
  A:
  
  I think this might be your issue:
  You initialise y at the beginning,
  dim y as long
  
  and by default it has the value 0. You make this assumption during your loop  
  Do
      Rng.Offset(0, y + 1).GoalSeek Goal:=0, ChangingCell:=Rng2.Offset(0, y + 1)
      y = y + 1
  Loop Until y = 12
  
  For the first sheet, y=0,1,2,3,...,12 then the loop stops. Then you leave y=12 and move onto your next sheet.
  So for the second sheet you go into the Do loop with y=12 and get an error. You should amend your code to the following:
  y = 0
  Do
      Rng.Offset(0, y + 1).GoalSeek Goal:=0, ChangingCell:=Rng2.Offset(0, y + 1)
      y = y + 1
  Loop Until y = 12
  
  If you'd used a Do While ... Loop instead, you would probably have noticed this sooner because the loop would never even have been entered as the condition wasn't met.
  y = 0
  Do While y < 12
      Rng.Offset(0, y + 1).GoalSeek Goal:=0, ChangingCell:=Rng2.Offset(0, y + 1)
      y = y + 1
  Loop 
  
  Really though, for a loop this simple, a For loop will always be easier to diagnose than a Do loop...
  For y = 0 to 11
      Rng.Offset(0, y + 1).GoalSeek Goal:=0, ChangingCell:=Rng2.Offset(0, y + 1)
  Next y
  
  