Goalseek doesn't work within VBA loop

[mellows23]

Hi All,

I've got a goalseek within a VBA loop which comes up with an error. It works in isolation, and I've checked the obvious things like the cells in the goalseek linking and being formulae.

I've turned auto-calc off, would this have an effect?

Code below, bit where is goes wrong is highlighted in red.

Any help would be much appreciated!

Private Sub CommandButton1_Click()




'MONTE CARLO VBA


Dim A As Integer
Dim B As Integer


'capture no of iterations


B = Sheet9.Range("o1")


'START LOOP


For A = 1 To B


'TURN OFF AUTO CALC


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False


'MOVE RAND TO RAND CALC


Sheet1.Range("M14:M168").Copy
Sheet1.Range("L14:L168").PasteSpecial xlPasteValues




'ADD BACK FORMULA FOR GOAL SEEK


Sheet1.Range("H67:H68").Copy
Sheet1.Range("L67:L68").PasteSpecial xlFormulas


Sheet1.Range("H70").Copy
Sheet1.Range("L70").PasteSpecial xlFormulas


Sheet1.Range("H72:H76").Copy
Sheet1.Range("L72:L76").PasteSpecial xlFormulas




'DO THE GOALSEEK ON THE RANDOM COLUMN


Sheet1.Range("l67:L76").Calculate


Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
Range("L76").GoalSeek Goal:=0, ChangingCell:=Range("L68")


'COPY OVER TO THE FIRST COLUMN


Sheet1.Range("l14:l168").Copy
Sheet1.Range("b14:B168").PasteSpecial xlPasteValues
Application.Calculate


'GET KPIS OFF OUTPUT SHEET


Sheet6.Range("b89:B98").Copy
Sheet9.Range("B2").Offset(A, 0).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.DisplayAlerts = True


Next


'TURN AUTO CALC BACK ON


Application.Calculation = xlAutomatic


'SET UP THE SCENARIO COLUMN TO FORMULAS AGAIN


Sheet1.Range("b13").Copy
Sheet1.Range("b14:B168").PasteSpecial xlFormulas




End Sub

 

[JoeMo]

What sheet are the goal seek cell and the changing cell on? Is that the activesheet when the code is run? If it's Sheet1, it seems you have put a formula in the changing cell earlier in your code on this line:

Sheet1.Range("L67:L68").PasteSpecial xlFormulas

unless the copied cells Sheet1.Range("H67:H68") do not have formulas.

 

[mellows23]

No, H68 and therefore subsequently L68 is still a hard coded number....

Having a look again now. I'll let you know if I get anywhere, but any suggestions are welcome!