worksheetfunction.vlookup in vba

[pmoon7]

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.

[Kevin Vaughn]

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
>
>

[Kevin Vaughn]

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
> >
> >

[JMB]

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
>
>

[pmoon7]

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.