• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

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
0
drhamel69
Asked:
drhamel69
  • 4
  • 3
1 Solution
 
StephenJRCommented:
How are you intending to use this array?
0
 
drhamel69Author Commented:
I have even more coding after this to loop through other things.
0
 
StephenJRCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
drhamel69Author Commented:
that would work too as long as I am passed an array back
0
 
StephenJRCommented:
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
        sAddr = rFind.Address
        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)
        Loop While rFind.Address <> sAddr
    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

Open in new window

0
 
drhamel69Author Commented:
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
 
StephenJRCommented:
I didn't put in any error trapping. It means the period value isn't found in the first row of your range.
0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now