Using Vlookup worksheet function in VBA

[marcbruun]

Hi Excel users,

I have a problem with my code that I cant solve. I have tried
everything and I think it is very close to succeed.

The code (UDF) below loops through the string in a cell and looks up a
word from a range with the vlookup.
The code returns #value no matter what i do.


Function ShowBrandName(celle As Range) As String

Dim i As Integer
Dim BrandName As String
Dim BrandsArea As Range

BrandName = ""
BrandsArea = Sheets("Lister").Range("J3:k7")

For i = Len(celle) To 1 Step -1

If IsError(Application.WorksheetFunction.VLookup(i, BrandsArea, 2,
False)) = False Then
ShowBrandName = BrandName &
Application.WorksheetFunction.VLookup(i, BrandsArea, 2, False) & ","
End If
Next i

End Function


Example:

A1 = "N,P"
B1 = "=ShowBrandName(A1)"

B1 returns #Value but should return "Nike,Puma,"

In sheet "Lister" table can be found in colum J and K.

J K
3 P Puma
4 K Nike
5 C Cola
6 S Stickie
7 U Ulster

 

[marcbruun]

Thanks Tom,

I tried that but I doesnt return anything now. Not an error etc.

Marc