Alternative to an Index/Match formula

[FryGirl]

I'm using VBA to write a formula (CSE) to a sheet with would be applied over a few thousand rows.

=INDEX('UMD Data'!A:A,MATCH(B2&"|"&D2&"|"&"Y",'UMD Data'!D:D&"|"&'UMD Data'!I:I&"|"&'UMD Data'!B:B,0))

Looking at speeding up this process as it really bogs down the processor speed, is there an alternative that could be more efficient?

 

[jasonb75]

Your biggest problem is the last part of the formula where you're joining the cells of 3 entire columns, reducing that to a range of 10k rows would reduce the processing effort required by ~99%

 

[FryGirl]

In VBA, I can establish the last row, but how can I make that part of the formula dynamic to receive the proper range?

 

[jasonb75]

Using rng as the range to write the formula to and lRow as the variable for the last row try this one.

rng.Formula = "=INDEX('UMD Data'!A$2:A" & lRow & ",MATCH(B2&""|""&D2&""|Y"",'UMD Data'!D$2:D" & lRow & "&""|""&'UMD Data'!I$2:I" & lRow & "&""|""&'UMD Data'!B$2:B" & lRow & ",0))"

Only done a quick edit in the forum but not tested it, hopefully I have it right.

 

[Fluff]

As it's an array formula, you need to put it in one cell & then copy down, like

Sub FryGirl()
   Dim UsdRws As Long
   
   UsdRws = Sheets("UMD Data").Range("A" & Rows.count).End(xlUp).Row
   With Sheets("Sheet1")
      .Range("C2").FormulaArray = "=INDEX('UMD Data'!A$2:A$" & UsdRws & ",MATCH(B2&""|""&D2&""|Y"",'UMD Data'!D$2:D$" & UsdRws & "&""|""&'UMD Data'!I$2:I$" & UsdRws & "&""|""&'UMD Data'!B$2:B$" & UsdRws & ",0))"
      .Range("C2:C" & .Range("B" & Rows.count).End(xlUp).Row).FillDown
   End With
End Sub

 

[FryGirl]

Thank you both Jason and Fluff. This works great. Yes, I learned the hard way you can't fill down an array formula the way you do a regular formula.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[jasonb75]

Thanks for picking that up, Fluff. I forgot about the array and wrote it as a regular formula

 

[Fluff]

No worries.