Using Vlookup worksheet function in VBA
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 |
Using Vlookup worksheet function in VBA
Thanks Tom, I tried that but I doesnt return anything now. Not an error etc. Marc On 18 Dec., 17:41, Tom Ogilvy wrote: Function ShowBrandName(celle As Range) As String Dim i As Integer Dim BrandName As String Dim BrandsArea As Range BrandName = "" set BrandsArea = Sheets("Lister").Range("J3:k7") For i = Len(celle) To 1 Step -1 If IsError(Application.VLookup(i, BrandsArea, 2, _ False)) = False Then BrandName = BrandName & _ Application.VLookup(i, BrandsArea, 2, False) & "," End If Next i ShowBrandName = BrandName End Function -- Regards, Tom Ogilvy " wrote: 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- Skjul tekst i anførselstegn -- Vis tekst i anførselstegn - |
All times are GMT +1. The time now is 07:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com