Goalseek Slow in Excel 2010

[ssayre]

I am using Excel 2010 in Windows 7. The macro below was created in Excel 2003 and is extremely slow in Excel 2010. The macro calculates a series of cash flows based on a number of other inputs. Any ideas on how to rewrite this code to work faster in Excel 2010?

The following is my code--simple, yet slow.

Sub GoalSeekNQ()

Range("c45").GoalSeek Goal:=0, ChangingCell:=Range("c37")
Range("d45").GoalSeek Goal:=0, ChangingCell:=Range("d37")
Range("e45").GoalSeek Goal:=0, ChangingCell:=Range("e37")
Range("f45").GoalSeek Goal:=0, ChangingCell:=Range("f37")
Range("g45").GoalSeek Goal:=0, ChangingCell:=Range("g37")
Range("h45").GoalSeek Goal:=0, ChangingCell:=Range("h37")
Range("i45").GoalSeek Goal:=0, ChangingCell:=Range("i37")
Range("j45").GoalSeek Goal:=0, ChangingCell:=Range("j37")
Range("k45").GoalSeek Goal:=0, ChangingCell:=Range("k37")
Range("l45").GoalSeek Goal:=0, ChangingCell:=Range("l37")
Range("m45").GoalSeek Goal:=0, ChangingCell:=Range("m37")
Range("n45").GoalSeek Goal:=0, ChangingCell:=Range("n37")
Range("o45").GoalSeek Goal:=0, ChangingCell:=Range("o37")
Range("p45").GoalSeek Goal:=0, ChangingCell:=Range("p37")
Range("q45").GoalSeek Goal:=0, ChangingCell:=Range("q37")
Range("r45").GoalSeek Goal:=0, ChangingCell:=Range("r37")
Range("s45").GoalSeek Goal:=0, ChangingCell:=Range("s37")
Range("t45").GoalSeek Goal:=0, ChangingCell:=Range("t37")
Range("u45").GoalSeek Goal:=0, ChangingCell:=Range("u37")
Range("v45").GoalSeek Goal:=0, ChangingCell:=Range("v37")
Range("w45").GoalSeek Goal:=0, ChangingCell:=Range("w37")
Range("x45").GoalSeek Goal:=0, ChangingCell:=Range("x37")
Range("y45").GoalSeek Goal:=0, ChangingCell:=Range("y37")
Range("z45").GoalSeek Goal:=0, ChangingCell:=Range("z37")
Range("aa45").GoalSeek Goal:=0, ChangingCell:=Range("aa37")
Range("ab45").GoalSeek Goal:=0, ChangingCell:=Range("ab37")
Range("ac45").GoalSeek Goal:=0, ChangingCell:=Range("ac37")
Range("ad45").GoalSeek Goal:=0, ChangingCell:=Range("ad37")
Range("ae45").GoalSeek Goal:=0, ChangingCell:=Range("ae37")
Range("af45").GoalSeek Goal:=0, ChangingCell:=Range("af37")
Range("ag45").GoalSeek Goal:=0, ChangingCell:=Range("ag37")
Range("ah45").GoalSeek Goal:=0, ChangingCell:=Range("ah37")
Range("ai45").GoalSeek Goal:=0, ChangingCell:=Range("ai37")
Range("aj45").GoalSeek Goal:=0, ChangingCell:=Range("aj37")
Range("ak45").GoalSeek Goal:=0, ChangingCell:=Range("ak37")
Range("al45").GoalSeek Goal:=0, ChangingCell:=Range("al37")
Range("am45").GoalSeek Goal:=0, ChangingCell:=Range("am37")
Range("an45").GoalSeek Goal:=0, ChangingCell:=Range("an37")
Range("ao45").GoalSeek Goal:=0, ChangingCell:=Range("ao37")
Range("ap45").GoalSeek Goal:=0, ChangingCell:=Range("ap37")

End Sub

 

[Biz]

Code not tested. Is it faster now?

Sub GoalSeekNQ2()
Dim a As Long, b As Long, i As Long
'Speeding Up VBA Code
With Application
    .ScreenUpdating = False 'Prevent screen flickering
    .Calculation = xlCalculationManual 'Preventing calculation
    .DisplayAlerts = False 'Turn OFF alerts
    .EnableEvents = False 'Prevent All Events
End With
a = 37
b = 45
For i = 3 To 42
Cells(b, i).GoalSeek Goal:=0, ChangingCell:=Range(a, i)
Next i
'Speeding Up VBA Code
With Application
    .ScreenUpdating = True 'Prevent screen flickering
    .Calculation = xlAutomatic 'Preventing calculation
    .DisplayAlerts = True 'Turn OFF alerts
    .EnableEvents = True 'Prevent All Events
End With
End Sub

 

[ssayre]

Turning off the screen updating helped significantly. I recieved an error message on the code line that reads:

Cells(b, i).GoalSeek Goal:=0, ChangingCell:=Range(a, i)

Even with my existing somewhat "klunky" code, turning off the screen referesh makes a big difference.

Thanks for the help.

 

[Biz]

Can you post your sample file?

Biz