VBA to speed up Array Formulas

[Chris_Downes]

Hi, I've done multiple search's online and never managed to find a solution that works. I'm not great at VBA (I can cut and paste from Google and change a few references to make a copy/paste work, but thats it), however I'm familiar enough with formula's/excel in general.

The issue I have is I have two worksheets which I need to cross reference and out put the result. Vlookup doesn't work as I need to result two outputs for the value being looked up. I can do this with an array formula according to the method posted here [LINK], however this results in a crazy number of calculations when looking thorugh thousands of rows and takes several minutes to complete and on some PC's in the business (with low RAM) can cause Excel to crash due to lack of memory.

I have read that this could be sped up using VBA, however I can't find a solution. The below code is the nearest I got after attempting to butcher a few examples from Google, however what I believe I need to do is write the results once into an array whithin VBA, then put the results back into the spreadsheet in one block, rather than looping through row by row. However I don't have the knowledge to do so! Can anyone help please?

My VBA code so far...

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Application.ScreenUpdating = False
Dim i As Long
Dim Result As String
For i = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(i, 1) = Lookupvalue Then
For J = 1 To i - 1
If LookupRange.Cells(J, 1) = Lookupvalue Then
If LookupRange.Cells(J, ColumnNumber) = LookupRange.Cells(i, ColumnNumber) Then
GoTo Skip
End If
End If
Next J
Application.Calculation = xlCalculationManual
Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & ","
Skip:
End If
Next i
MultipleLookupNoRept = Left(Result, Len(Result) - 1)
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Function

My Array formula's..
{=IFERROR(INDEX('MB52'!$C:$C, SMALL(IF($A5='MB52'!$E:$E, ROW('MB52'!$E:$E)-MIN(ROW('MB52'!$E:$E))+1, ""), COLUMN(A2))),"")}
Where "MB52" is the sheet being looked up and is several thousand rows long, and the lookup value is in column A of the sheet the Array formula is located.

 

[Fluff]

Before going down a VBA route, I would suggest changing your formula so that it does not look at entire columns.
So if you would normally expect to have 2000 rows of data maximum use
{=IFERROR(INDEX('MB52'!$C$2:$C$5000, SMALL(IF($A5='MB52'!$E$2:$E$5000, ROW('MB52'!$E$2:$E$5000)-MIN(ROW('MB52'!$E$2:$E$5000))+1, ""), COLUMN(A2))),"")}
Alternatively do you have the dynamic array functions, like Unique, Filter etc

 

[Chris_Downes]

Hi Fluff,

That has made quite a difference. I've never really had an issue before with blank lines taking up so much time (i.e. in vlookup etc.) but it must be adding a lot of resource with the Arrays. I'll get one of my team to try it on a slower PC (I've 32GB of RAM and 64 bit excel so I wasn't having much issue in the first place compared to some).

Thanks!

 

[Fluff]

You should always avoid whole column references, especially with array formulae.

 

[Fluff]

If you have the new dynamic arrays functions, a even better option would be
=TRANSPOSE(FILTER('MB52'!$C$2:$C$5000,'MB52'!$E$2:$E$5000=A5))

 

[Chris_Downes]

It doesn't appear that I have the filter dynamic array function. Shame really as that seems like a much slicker way of doing it than my original forumla.