Faster Goal Seek / Copy Past Macro

[chet645]

All,

I have created a macro to automate a bunch of scenarios I am running in a financial model I've built. The ultimate goal is to automate 30K+ scenarios with my macro. The problem is, the macro I've written (below) is far to slow (it would take me two days at the current speed to run all of the scenarios).

Ultimately, my macro is a simple goal seek (i.e. goal seek the purchase price of an investment that meets a given financial return requirement) that copies and pastes the resulting purchase price to a cell after the goal seek is complete.

GOAL SEEK MACRO:

Sub Sensitivity_Macro()


Dim Macro As Integer
Dim A As Integer
Dim B As Integer


A = Range("Macro_Start")
B = Range("Macro_End")


For Macro = A To B Step 1


Application.Goto Reference:="Macro_No"
ActiveCell.Value = Macro


Range("Min_Delta").GoalSeek Goal:=0, ChangingCell:=Range("Land_Value")


Application.Goto Reference:=("Land_Paste")
ActiveCell.Offset(Macro, 0).Range("a1").Select
Range("Land_Copy").Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False


Next Macro


End Sub



In order for me to accomplish my task, speeding up my macro is a must. My macro needs to calculate each scenario in under 3 seconds for it to make my life easy.

This forum has always been a huge help. Would love to get your ideas once again.

Best Regards,

Chet

 

[JoeMo]

Add Application.Screenupdating = False at the start of your code.
Add Application.Screenupdating = True at the end of the code.

Get rid of all the selecting which takes time. For example, these two lines:

Application.Goto Reference:="Macro_No"
ActiveCell.Value = Macro

can be reduced to this:

Range("Macro_No").Value = Macro

If there are many formulas in your sheet then you may want to set calculation to manual as well.

 

[chet645]

Thanks for your help, Joe. Those suggestions made a difference.

I was reading some stuff on-line, and it looks like my copy and paste code is pretty inefficient. I read that the following Range(“B1?).Value = Range(“A1?).Value is a significantly faster way copy and paste. Problem is, how would I use this more efficient code with the offset function in my current copy paste code? Essentially, the offset function tells the macro to paste the result in the row associated with the particular scenario.

Any thoughts?