Excel, VBA Vlookup, multiple returns into rows
- by Sean Mc
Very new to VBA, so please excuse my ignorance.
How would you alter the code below to return the result into rows as opposed to a string?
Thanks in advance....
data
Acct No CropType
------- ---------
0001 Grain
0001 OilSeed
0001 Hay
0002 Grain
function
=vlookupall("0001", A:A, 1, " ")
Here is the code:
Function VLookupAll(ByVal lookup_value As String, _
ByVal lookup_column As range, _
ByVal return_value_column As Long, _
Optional seperator As String = ", ") As String
Application.ScreenUpdating = False
Dim i As Long
Dim result As String
For i = 1 To lookup_column.Rows.count
If Len(lookup_column(i, 1).text) <> 0 Then
If lookup_column(i, 1).text = lookup_value Then
result = result & (lookup_column(i).offset(0, return_value_column).text & seperator)
End If
End If
Next
If Len(result) <> 0 Then
result = Left(result, Len(result) - Len(seperator))
End If
VLookupAll = result
Application.ScreenUpdating = True
End FunctionNotes:
