Solved

# XY Look up function for Excel range.  Return array of results to VBA subroutine

Posted on 2012-04-11
481 Views
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.

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
0
Question by:drhamel69
• 4
• 3

LVL 24

Expert Comment

How are you intending to use this array?
0

LVL 2

Author Comment

I have even more coding after this to loop through other things.
0

LVL 24

Expert Comment

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.
0

LVL 2

Author Comment

that would work too as long as I am passed an array back
0

LVL 24

Accepted Solution

StephenJR earned 500 total points
Here is one approach. I'm assuming you want a 2x2 array returned?
``````Function XYLookup(rTable As Range, vName, vPeriod) As Variant

Dim vOut(), i As Long, c As Long, rFind As Range, sAddr As String

c = Application.Match(vPeriod, rTable.Rows(1), 0)

With rTable.Columns(2)
Set rFind = .Find(What:=vName, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rFind Is Nothing Then
Do
i = i + 1
ReDim Preserve vOut(1 To 2, 1 To i)
vOut(1, i) = rFind.Offset(, -1)
vOut(2, i) = rTable(rFind.Row - rTable(1).Row + 1, c)
Set rFind = .FindNext(rFind)
End If
End With

XYLookup = Application.Transpose(vOut)

End Function

Sub x()

Dim v, i As Long

v = XYLookup(Range("A2:H11"), "Joe", "Period 4")

For i = LBound(v, 1) To UBound(v, 1)
MsgBox v(i, 1) & "," & v(i, 2)
Next i

End Sub
``````
0

LVL 2

Author Comment

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?
0

LVL 24

Expert Comment

I didn't put in any error trapping. It means the period value isn't found in the first row of your range.
0

LVL 45

Expert Comment

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

## Featured Post

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…