Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Syntax for VLOOKUP to return array of return values
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Syntax for VLOOKUP to return array of return values
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Syntax for VLOOKUP to return array of return values
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Syntax for VLOOKUP to return array of return values
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Syntax for VLOOKUP to return array of return values
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Syntax for VLOOKUP to return array of return values
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Syntax for VLOOKUP to return array of return values
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return values from an array based on more than one criteria | Excel Discussion (Misc queries) | |||
How to return an array of values that are not zero/Null | Excel Discussion (Misc queries) | |||
How to return an array of values that are not zero/Null | Excel Discussion (Misc queries) | |||
Return an array of results with a vlookup | Excel Discussion (Misc queries) | |||
How to use an array or matrix to return text vs. numeric values | Excel Worksheet Functions |