Change formula to VBA Index Match Multi match values

[justvba]

I m trying to speed up my VBA and I think it is because of the 3 columns that have foumulasin them.
I am putting a forumula in cells B2(dl1), C2(dl2), D2(dl3) then autofilling to the last line my code is as follows


/code

dl1 = Application.Match("Deal #1", Rows(1), 0)
dl2 = Application.Match("Deal #2", Rows(1), 0)
dl3 = Application.Match("Deal #3", Rows(1), 0)

With ActiveSheet
LASTROW = .Cells(.Rows.Count, "E").End(xlUp).Row
End With
Cells(2, dl1).Select
Selection.FormulaArray = "=IFERROR(INDEX(C6,MATCH(RC1&R1C2,C1&C5,0)),"""")"
Cells(2, dl2).Select
Selection.FormulaArray = "=IFERROR(INDEX(C6,MATCH(RC1&R1C3,C1&C5,0)),"""")"
Cells(2, dl3).Select
Selection.FormulaArray = "=IFERROR(INDEX(C6,MATCH(RC1&R1C4,C1&C5,0)),"""")"
Range(Cells(2, dl1), Cells(2, dl3)).AutoFill Destination:=Worksheets("Testing").Range(Cells(2, dl1), Cells(LASTROW, dl3))


/code

FYI the formula is writen as follows

{=IFERROR(Index($F:$F), Match( $A2&$B$1,$A:$A&$E:$E,0)),"")}

 

[alansidman]

You may want to set the screen updating to false at the beginning of your code and setting to True at the end of the code. Likewise you may want to turn off auto calc at the beginning and reset it to auto calc at the end of your code. This may speed up your VBA.

 

[justvba]

You may want to set the screen updating to false at the beginning of your code and setting to True at the end of the code. Likewise you may want to turn off auto calc at the beginning and reset it to auto calc at the end of your code. This may speed up your VBA.

I just copy and paste value only as soon as the formula is entered and I have screenupdate off. this is the only part of the code that takes about 2 mins to run.

 

[justvba]

this sped it up a little but not much

With ActiveSheet
LASTROW = .Cells(.Rows.Count, "E").End(xlUp).Row
End With

Do Until ActiveCell.Row > LASTROW
With ActiveSheet
ActiveCell.FormulaArray = "=IFERROR(INDEX(C6,MATCH(RC1&R1C2,C1&C5,0)),"""")"
ActiveCell.Offset(0, 1).Activate
Selection.FormulaArray = "=IFERROR(INDEX(C6,MATCH(RC1&R1C3,C1&C5,0)),"""")"
ActiveCell.Offset(0, 1).Activate
Selection.FormulaArray = "=IFERROR(INDEX(C6,MATCH(RC1&R1C4,C1&C5,0)),"""")"

End With
Range(ActiveCell, ActiveCell(1, -1)).Select
Selection.Copy
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
ActiveCell.Select
Selection.Offset(1, 0).Activate
Loop

 

[alansidman]

Have you tried turning off Auto Calc as I suggested earlier. I do not see this in your current code.

 

[Marcelo Branco]

I m trying to speed up my VBA and I think it is because of the 3 columns that have foumulasin them.
I am putting a forumula in cells B2(dl1), C2(dl2), D2(dl3) then autofilling to the last line my code is as follows

FYI the formula is writen as follows

{=IFERROR(Index($F:$F), Match( $A2&$B$1,$A:$A&$E:$E,0)),"")}

Your code is not fast because you are using references to entire columns in an array formula like C1&C5 (A:A&E:E in the spreadsheet formula), ie., concatenating two columns with 1,048,576 rows each. Such references, in array formulas, impair seriously the performance.

EDITED
Try something like this

Cells(2, dl1).FormulaArray = "=IFERROR(INDEX($F$2:$F$" & LASTROW & _
    ",MATCH($A2&B$1,E$2:E$" & LASTROW & "&$F$2:$F$" & LASTROW & ",0)),"""")"
Cells(2, dl2).FormulaArray = "=IFERROR(INDEX($F$2:$F$" & LASTROW & _
    ",MATCH($A2&C$1,E$2:E$" & LASTROW & "&$F$2:$F$" & LASTROW & ",0)),"""")"
Cells(2, dl3).FormulaArray = "=IFERROR(INDEX($F$2:$F$" & LASTROW & _
    ",MATCH($A2&D$1,E$2:E$" & LASTROW & "&$F$2:$F$" & LASTROW & ",0)),"""")"
    
Range(Cells(2, dl1), Cells(2, dl3)).AutoFill _
    Destination:=Worksheets("Testing").Range(Cells(2, dl1), Cells(LASTROW, dl3))

M.

 

[Marcelo Branco]

I edited the code above to set absolute references (including $) in columns E and F.

M.

 

[Marcelo Branco]

oops... sorry, i concatenated the wrong columns E&F - should be A&E

Try

Cells(2, dl1).FormulaArray = "=IFERROR(INDEX($F$2:$F$" & LASTROW & _
    ",MATCH($A2&B$1,$A$2:$A$" & LASTROW & "&$E$2:$E$" & LASTROW & ",0)),"""")"
Cells(2, dl2).FormulaArray = "=IFERROR(INDEX($F$2:$F$" & LASTROW & _
    ",MATCH($A2&C$1,$A$2:$A$" & LASTROW & "&$E$2:$E$" & LASTROW & ",0)),"""")"
Cells(2, dl3).FormulaArray = "=IFERROR(INDEX($F$2:$F$" & LASTROW & _
    ",MATCH($A2&D$1,$A$2:$A$" & LASTROW & "&$E$2:$E$" & LASTROW & ",0)),"""")"

Range(Cells(2, dl1), Cells(2, dl3)).AutoFill _
    Destination:=Worksheets("Testing").Range(Cells(2, dl1), Cells(LASTROW, dl3))

M.