Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel VLOOKUP hyperlink to mailto function on different worksheet | Excel Worksheet Functions | |||
Worksheet function Vlookup | Excel Programming | |||
Vlookup worksheet function | Excel Worksheet Functions | |||
How to use a cell value as Table Array in VLOOKUP worksheet function | Excel Discussion (Misc queries) | |||
Application Worksheet Function Vlookup error | Excel Programming |