VBA Formula Takes too long

[Daniellel]

Hi,

I have written some VBA code to insert a formula into approx 450+ cells. The formula is...

Selection.FormulaArray = _
"=INDEX(TO3!$C$1:$I$9999,MATCH($A9&$B9,TO3!$A$1:$A$9999&TO3!$B$1:$B$9999,0),COLUMNS(TO3!$C2:C2))"

This has now added about 30 mins onto my VBA update and the list is only going to get bigger, is there anything i can do to speed this up???

 

[Trevor G]

Oops good spot Jon

Should be

Sub autoUpdate()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

'Do your code then switch back on

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True


End Sub

 

[Daniellel]

Oops good spot Jon

Should be

Its now a bit quicker,

Thank you

 

[jkpieterse]

It is probably a lot faster if you add helper columns which combine the two strings you are searching for and then use a non-array formula to do the INDEX/MATCH.

If the list you are looking up can be sorted on the lookup column, it may be even faster to do a non-exact match.

Suppose you want to match a value in Column A of sheet1 and return the associated value in column B. Column A is sorted.
Normally you would do either:

=VLOOKUP(Sheet1!$A$1:$B$1000,2,False)

or
=INDEX(Sheet1!$B$1:$B$1000,MATCH(A1,Sheet1!$A$1:$A$1000,0))

Both doing an exact match search.

This approach however is as much as 3 to 5 times faster:

=IF(INDEX(Sheet1!$A$1:$A$1000,MATCH(A1,Sheet1!$A$1:$A$1000,1))=A1,INDEX(Sheet1!$B$1:$B$1000,MATCH(A1,Sheet1!$A$1:$A$1000,1)),NA())

 

[Daniellel]

This approach however is as much as 3 to 5 times faster:

=IF(INDEX(Sheet1!$A$1:$A$1000,MATCH(A1,Sheet1!$A$1:$A$1000,1))=A1,INDEX(Sheet1!$B$1:$B$1000,MATCH(A1,Sheet1!$A$1:$A$1000,1)),NA())

This is returning NA# ???

 

[jkpieterse]

Is the source list sorted on the lookup column?