VBA Syntax for VLOOKUP to return array of return values
I got this with 10035 in A3
1 B3
2 C3
3 D3
4 E3
As Dave said, if there is no match, just as in a worksheet, you get an error
if there is no match.
? CVErr(xlErrNA)
Error 2042
Regards,
Tom Ogilvy
Alan Beban wrote in message
...
Tom,
I'm using xl2000. When I ran the code step by step it went from
If IsArray(varr) Then to End If. When I ran it again with
Debug.Print varr immediately after the Evaluate line it printed Error
2042. Did you get something different? In what version?
Thanks,
Alan Beban
Tom Ogilvy wrote:
Generally, to do an array formula you need to use evaluate:
Sub TestVlook()
Dim varr As Variant
Dim i As Long
varr = Evaluate("VLookup(10035,A1:E5,{2, 3, 4, 5}, False)")
If IsArray(varr) Then
For i = LBound(varr) To UBound(varr)
Debug.Print i, varr(i)
Next
End If
End Sub
Regards,
Tom Ogilvy
Alan Beban wrote in message
...
Rob Bovey, Dave Peterson and Dana DeLouis recently provided helpful
suggestions for a similar situation with COUNTIF. None of them seems to
resolve the following superficially similar problem with VLOOKUP.
=VLOOKUP(10035,A1:E5,{2,3,4,5},FALSE)
works fine on the worksheet.
With the VLookups function from my website, either of the following VBA
code snippets works as well:
arr = VLookups(10035, Range("tbl2"),Application.Evaluate("{2,3,4,5}"))
arr = VLookups(10035, Range("tbl2"), Evaluate("{2,3,4,5}"))
But notwithstanding the above helpful suggestions for COUNTIF, I can't
get anything like the following to work:
arr = Application.VLookup(10035,A1:E5,????????,False)
Any more helpful suggestions?
Thanks in advance,
Alan Beban
|