Link to home
Start Free TrialLog in
Avatar of drhamel69
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
Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image

How are you intending to use this array?
Avatar of drhamel69
drhamel69

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.
that would work too as long as I am passed an array back
ASKER CERTIFIED SOLUTION
Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
I didn't put in any error trapping. It means the period value isn't found in the first row of your range.
Avatar of Martin Liss
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.