drhamel69
asked on
XY Look up function for Excel range. Return array of results to VBA subroutine
Any direction or help would be appreciated. I have attached a file for you to look at. What I need is basically a XY table lookup in VBA BUT there can be more than 1 result. And I need it to ignore blank values and blank lines.
Please refer
For example, if I pass Joe and Period 4 to the funciton. I would expect an array back with 2 elements of France, .75 and Italy, 1
It also needs to ignore blank lines and numbers.
XYlookup.xlsx
Please refer
For example, if I pass Joe and Period 4 to the funciton. I would expect an array back with 2 elements of France, .75 and Italy, 1
It also needs to ignore blank lines and numbers.
XYlookup.xlsx
How are you intending to use this array?
ASKER
I have even more coding after this to loop through other things.
OK, didn't read your question title properly. I would have a procedure with arguments including the range and then no need to bother about whether there are blank lines.
ASKER
that would work too as long as I am passed an array back
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
WOrks fine on the example I gave but when I imported into my actual project i am getting a type mismatch error on c = Application.Match(vPeriod, rTable.Rows(1), 0)
Any Ideas?
Any Ideas?
I didn't put in any error trapping. It means the period value isn't found in the first row of your range.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.