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

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

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

You'll get the error 2042 if there wasn't a match for the 10035 in A1:A5.

Is it missing or text?



Alan Beban wrote:

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





--

Dave Peterson

Hi, Alan:

My experience with evaluating array formulas in VBA has been that it can't be trusted. One day
the code will work, the next day -- without any changes to the worksheet or the code -- it
doesn't.

So I've given up on that and write the required loops.

Myrna Larson

On Mon, 04 Aug 2003 17:20:47 -0700, Alan Beban wrote:

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

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

It's working, thanks. I think my problem was that the right worksheet
wasn't active. When I was playing with it originally, I was using a
named range instead of A1:E5, so that it didn't matter what sheet was
active; so I wasn't careful when I shifted over to experimenting with
Tom's code, which of course had in it the A1:E5 that I provided, and
that refers to the active sheet.

So now I'll go see if the same syntax will work with the COUNTIF situation.

Thanks for the help,
Alan Beban

Tom Ogilvy wrote:
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

Hi Myrna,

One reason for your problems may be that using Application.Evaluate with
references unqualified by sheets always refers to the active sheet, so you
get a different answer depending on which sheet is active.
You can use Worksheet.Evaluate to control this behaviour.


hth
Charles Williams
www.DecisionModels.com

"Myrna Larson" wrote in message
...
Hi, Alan:

My experience with evaluating array formulas in VBA has been that it can't
be trusted. One day
the code will work, the next day -- without any changes to the worksheet
or the code -- it
doesn't.

So I've given up on that and write the required loops.

Myrna Larson

On Mon, 04 Aug 2003 17:20:47 -0700, Alan Beban wrote:

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