How is this code faster?

[ScottUlmer]

Faster Code:

Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A" & lastrow), Type:=xlFillDefault
Range("A3:A" & lastrow).Select
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Slower Code:

Range("A3:A" & LastRow).Formula = "=IFERROR(INDIRECT(CONCATENATE(" & """" & "Ref!$A" & """" & ",MATCH(B3,Ref!$C:$C,0))),INDIRECT(CONCATENATE(" & """" & "Ref!$A" & """" & ",MATCH(B3,Ref!$D:$D,0))))"
Range("A3:A" & LastRow).Value = Range("A3:A" & LastRow).Value


Now this type of code of having a formula and then filling down is used a lot. I figured if I simply did it as a range it would be better especially if I did not have to copy paste. The reason I am trying to make this change is our IT say they do not want people to be able to edit the formulas. The old code would also do ranges for example Rows D:Q and then fill down and I think this is where it is truly faster. I am thinking Excel is multi-threading and not just doing my vba code in a single thread. If I change the formulas into pure VBA for for loops and the such it is even slower.

Between no copy paste, less ".select", and also turning off screen updating I would be faster than the old code. However, the old code (full macro obviously) takes about 3 minutes, while my code (not fully converted) takes about 3.5 minutes.

Do you have any idea why the old code is faster or how to make mine better? Thanks!

 

[mole999]

INDIRECT is volatile, so every change forces a recalculation > http://www.decisionmodels.com/calcsecretsi.htm

 

[ScottUlmer]

Cool thank you, I will read up on it. My question is though how is him filling down with the same formula faster than me doing a range = formula?

 

[offthelip]

The really fast way to do this is to use VBA to do the whole task. You finally end up with just values in Column A, so if you loaded the entire "Ref" sheet into a variant array and then used a VBA loop to find the match you were looking for your macro would take seconds. Have a look at this thread which shows you the basic technique for using VBA instead of Vlookup, but it works just as well instead of index and match, Also concatenation in vBA is very easy
https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html