Macro slows down after change in formula

user_19155885 0
Viewed 42

I used to have this formula below copied into multiple cells, but at some point it wasn't pulling the data properly so I had to modify it to the formula below it. The SUMPRODUCT formula finds data from another spreadsheet based off the part number and pulls it over to the new spreadsheet.

=IFERROR(VLOOKUP($B14,'G:\Locations_NA\TUS\LO\[DMSU MACRO DATA.xlsm]DDCPIV'!$A:$W,13,FALSE),0)

=IFERROR(SUMPRODUCT(('G:\Locations_NA\TUS\LO\[DMSU MACRO DATA.xlsm]DDC SD'!$F$4:$AT$10000)*('G:\Locations_NA\TUS\LO\[DMSU MACRO DATA.xlsm]DDC SD'!$A$4:$A$10000=$B14)*('G:\Locations_NA\TUS\LO\[DMSU MACRO DATA.xlsm]DDC SD'!$F$3:$AT$3=$AD$13)),0)

I run a macro that someone else made which, besides doing other things, fills down this formula a couple hundred rows. Now when I run it with the new formula it takes ages compared to how long it used to take. Is there a better way I can go about this to speed it up?

vba excel
1 Answers

I would recommend these three basic things, that will help you without modifying much your code.

First: Change VLOOKUP or SUMPRODUCT to "XLOOKUP". This is a faster new formula

it will be something like this

=IFERROR(XLOOKUP(R14C12, _
'G:\Locations_NA\TUS\LO\[DMSU MACRO DATA.xlsm]DDCPIV'!$A:$A, _
'G:\Locations_NA\TUS\LO\[DMSU MACRO DATA.xlsm]DDCPIV'!$W:$W,_ 
"Error",0),0)

Second: Try to set the formula by a bunch of cells, not one by one (I'm assuming this, if you share more of your code we can check.

one by one is:

Range("A1").FormulaR1C1 =IFERROR(XLOOKUP(R14C12 ...
Range("A2").FormulaR1C1 =IFERROR(XLOOKUP(R15C12 ...

Bunch of cell is:

Range("A1:A5").FormulaR1C1 =IFERROR(XLOOKUP(R14C12 ...

Third: as ENIAC just said, disable UpdatingScreen and other Automatic Updates that Excel has. This will be like this:

Sub Example() 
'Beginning Code   
With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .DisplayAlerts = False
End With

'Your code Here

'End Code
With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .DisplayAlerts = True
End With

End sub

Now, the best way, in my opinion, is to work with Arrays and Dictionaries, that system is much faster, very much.

user_143159260
Related