Help on making macro code run faster

[sohil2520]

Hi everyone,

I have a fairly large excel file (~21MB) with a macro which performs about 400 goalseek runs. This macro has to run everytime a change is made in any of the 13 key input cells, and hence, should be pretty fast. The problem is that it currently takes about 8-10 minutes to run, a time which reduces by half if i copy-paste the sheet into another workbook and run the code there. It'd be great if you can help me with speeding up the excel code.

Here's the code:

Sub MoU_Growth()

Dim i As Integer
Dim j As Integer

Sheets("MoU Projections (2)").Select
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 3 To 20

For j = 63 To 84

If Cells(j, i).Value <> 0 Then

Range(Cells(j + 27, i), Cells(j + 27, i)).Copy
Range("H59").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'to copy-paste current goal seek answers for a quicker goal seek run
Range(Cells(j, i), Cells(j, i)).GoalSeek Goal:=1, ChangingCell:=Range("H59")
Range("H59").Copy
Range(Cells(j + 27, i), Cells(j + 27, i)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.StatusBar = "Working on cell " & Chr(64 + i) & j​

End If​

Next j​

Next i
Application.ScreenUpdating = True
Application.StatusBar = "Complete"
Application.Calculation = xlCalculationAutomatic​

End Sub

 

[Jerry Sullivan]

Hi and Welcome,

The code could be improved a little to reduce some of the copying time, but my guess is that would be a negligible difference....a fraction of a second off your 8-10 minute run.

It's more likely that you could find speed improvements in the formulas in the worksheet data. I'm a little puzzled that you are copying data into H59, but then immediately using H59 as the changing cell.

If you will post your single worksheet to a host site, or exchange emails through a Private Message, I'd be glad to look at it to see if I can help.

 

[sohil2520]

Thanks JS411.
I copy-paste data into H59 so that the goal seek starts running from the previous goal seek solution. I did that hoping that it would reduce the number of iterations required for goal seek and hence, speed up the macro.
About sharing the spreadsheet, I will send you a private message with a version of the spreadsheet in about an hour from now