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

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

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

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

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