VBA to goalseek across column

Hi,

I need a VBA to code to goalseek multiple cells to 0 by changing multiple cells.

I have a range of set cells (EA44:GH44) to Value of 0 by changing cells (BP44:DW44). I have written a code but I am not sure if it works. I wanted to check before running the macro.

Public Sub A()
  Dim iRow         As Long
  Dim iCol          As Long

  For iRow = 44
    For iCol = Columns("BP").Column To Columns("DW").Column
      With Cells(iRow, iCol + 63)
        .GoalSeek Goal:=0, ChangingCell:=.Offset(, -63)
      End With
    Next iCol
End Sub

Thanks

Looks correct.

However, as with any macro/VBA codes you do, save the file first before running the macro. That way if something unexpected happens, you can revert to the last saved file.

OK. If I wanted to achieve this with an iteration would this work:

Sub Iterate ()
Dim i as Integer
For i = 1 to 100
Application.Calculate
Range("BP44:DW44").Value = Range ("EA44:GH44").Value
Next i
End Sub

Is using an iteration faster than a goalseek. At the moment my spreadsheet takes a while to process. Is there a way to make it quicker or perhaps some VBA code?

I agree with most of what MrShorty says above. It's hard to ascertain which method is faster without knowing a lot more.

However, if I were to hazard a guess, the 2nd option (For Loop) would almost produce a faster solution as you may be able to utilise more of your processor as you are solving for 63 solutions at 1 go, rather than solving for each 1 at a time.

Having said that, I would propose the following solution. Rather than use a For Loop where the number of iterations is fixed, I would propose using a Do Loop instead. That way, you only run enough iterations to achieve your objective, rather than running it 100 times when you only need 20 to solve.

Sub Iterate ()
Do Until Application.WorksheetFunction.Sum(Range("EA44:GH44")) = 0
Range("BP44:DW44").Value = Range ("EA44:GH44").Value
Application.Calculate
Loop
End Sub

Hi,

I have tried to use the VBA that you mentioned above and it comes up with a run time error 1004. "Unable to get the Sum property of the WorkSheetFucntion class."

Why is this?

The range EA44:GH44 are the cells that need to be goalseeked to 0.

Thanks

I have used the VBA code that I posed initially

Public Sub A()
  Dim iRow         As Long
  Dim iCol          As Long

  For iRow = 44
    For iCol = Columns("BP").Column To Columns("DW").Column
      With Cells(iRow, iCol + 63)
        .GoalSeek Goal:=0, ChangingCell:=.Offset(, -63)
      End With
    Next iCol
End Sub

When I try and run it it comes up with a syntax error message box with the line highlighted in red.

Can anyone help?

You don't need to loop for row numbers as you are only looking through 1 row.

Just use

iRow = 44
For iCol = ...
...
Next iCol

That seems to work. Thanks for that.