Chris_Downes
New Member
- Joined
- May 7, 2020
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
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...
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.
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...
VBA Code:
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.