Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.vlookup in vba
Function StLookup(strSearch As String, tableName As Range, position) StLookup = WorksheetFunction.VLOOKUP(strSearch, tableName, position, False) End Function I can't seem to trace the error #value return from this function. My original vlookup is getting wieldy -- too many ifs -- and I want to move it within the vba function so I can organize it better. -- pmoon7 ------------------------------------------------------------------------ pmoon7's Profile: http://www.excelforum.com/member.php...o&userid=30808 View this thread: http://www.excelforum.com/showthread...hreadid=504698 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.vlookup in vba
Worked for me. I temporarily got a #VALUE error when I tried substituting a
named range instead of using a range (a1:b4), but it turns out I had misspelled my range (I had created it as myrante instead of myrange.) As soon as I created the correctly named name range, it worked. I also managed to get a #VALUE error when I changed my range to a1:a4 (as I was using 2 for position.) Perhaps it is something like that? -- Kevin Vaughn "pmoon7" wrote: Function StLookup(strSearch As String, tableName As Range, position) StLookup = WorksheetFunction.VLOOKUP(strSearch, tableName, position, False) End Function I can't seem to trace the error #value return from this function. My original vlookup is getting wieldy -- too many ifs -- and I want to move it within the vba function so I can organize it better. -- pmoon7 ------------------------------------------------------------------------ pmoon7's Profile: http://www.excelforum.com/member.php...o&userid=30808 View this thread: http://www.excelforum.com/showthread...hreadid=504698 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.vlookup in vba
I see it also happens when the lookup value not found, but don't have time to
investigate why. -- Kevin Vaughn "Kevin Vaughn" wrote: Worked for me. I temporarily got a #VALUE error when I tried substituting a named range instead of using a range (a1:b4), but it turns out I had misspelled my range (I had created it as myrante instead of myrange.) As soon as I created the correctly named name range, it worked. I also managed to get a #VALUE error when I changed my range to a1:a4 (as I was using 2 for position.) Perhaps it is something like that? -- Kevin Vaughn "pmoon7" wrote: Function StLookup(strSearch As String, tableName As Range, position) StLookup = WorksheetFunction.VLOOKUP(strSearch, tableName, position, False) End Function I can't seem to trace the error #value return from this function. My original vlookup is getting wieldy -- too many ifs -- and I want to move it within the vba function so I can organize it better. -- pmoon7 ------------------------------------------------------------------------ pmoon7's Profile: http://www.excelforum.com/member.php...o&userid=30808 View this thread: http://www.excelforum.com/showthread...hreadid=504698 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.vlookup in vba
Worked okay for me. You will get an error if no matches are found.
You could make your wrapper function return a specified value (say 0) if VLOOKUP returns N/A (meaning it found no matches). Function StLookup(strSearch As String, tableName As Range, position) With Application If .IsNA(.VLookup(strSearch, tableName, position, False)) Then StLookup = 0 Else: StLookup = .VLookup(strSearch, tableName, position, False) End If End With End Function If you have a lot of lookups, you may want to reconsider using a UDF as they are slower than Excels native functions and could bog down your computer. "pmoon7" wrote: Function StLookup(strSearch As String, tableName As Range, position) StLookup = WorksheetFunction.VLOOKUP(strSearch, tableName, position, False) End Function I can't seem to trace the error #value return from this function. My original vlookup is getting wieldy -- too many ifs -- and I want to move it within the vba function so I can organize it better. -- pmoon7 ------------------------------------------------------------------------ pmoon7's Profile: http://www.excelforum.com/member.php...o&userid=30808 View this thread: http://www.excelforum.com/showthread...hreadid=504698 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheetfunction.vlookup in vba
Thank-you to both KV & JMB for helping. I used JMB's snippet and it worked and then I went back to the original and it worked. I still have no idea why it was not working before. -- pmoon7 ------------------------------------------------------------------------ pmoon7's Profile: http://www.excelforum.com/member.php...o&userid=30808 View this thread: http://www.excelforum.com/showthread...hreadid=504698 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WorksheetFunction.Vlookup | Excel Discussion (Misc queries) | |||
Worksheetfunction.vlookup and #value error | Excel Worksheet Functions | |||
Worksheetfunction.Vlookup | Excel Programming | |||
vlookup worksheetfunction | Excel Programming | |||
worksheetfunction.vlookup? | Excel Programming |