Exceladd1ct
Board Regular
- Joined
- Feb 10, 2019
- Messages
- 76
Hi, i am trying to get multiple results with WorksheetFunction.VLookup. The way i see it, i could use a function as bellow, but i got stuck getting the address of the value found by vLookUp Function.
I know it is easier to use Range.Find Method but it has a small delay on my big data set while WorksheetFunction.VLookup gets the results instantly.
I know it is easier to use Range.Find Method but it has a small delay on my big data set while WorksheetFunction.VLookup gets the results instantly.
VBA Code:
Function lookup(tofind, firstrow)
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("Data")
Dim myFirstColumn As Long
Dim myLastColumn As Long
Dim myColumnIndex as Long
Dim myLastRow As Long
Dim myVLookupResult
Dim allstr As String
Dim myTableArray As Range
myFirstColumn = 1
myLastColumn = 3
myColumnIndex = 3
myLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
With ws
Set myTableArray = .Range(.Cells(firstrow, myFirstColumn), .Cells(myLastRow, myLastColumn))
End With
On Error Resume Next
myVLookupResult = Application.WorksheetFunction.VLookup(tofind, myTableArray, myColumnIndex, False)
On Error GoTo 0
If myVLookupResult <> "" Then
'This is where it gets difficult, this is more like pseudocode
'Save the result in a delimited string to work with later
allstr = allstr & "|" & myVLookupResult
'Define the starting point for the next lookup'
firstrow = myVLookupResult.Address
'Recall function
Call lookup(tofind, firstrow + 1)
End If
End Function