Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Match Function (XP; SP3)

    I am having an issue trying to replicate a two dimensional MATCH cell formula in VBA.

    I have attached a sample file.

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Match Function (XP; SP3)

    Taking a union of ranges means that you create a range that consists of all cells that occur in at least one of the constituting ranges. This has nothing to do with concatenating values from two ranges.

    Others will correct me if I'm wrong, but I don't think you can use the WorksheetFunction object here, you'll probably have to loop through the rows:

    Sub TestTwoDimensional()
    Dim lngRow As Long
    For lngRow = 2 To 5
    If Range("A" & lngRow) = "Two" And Range("B" & lngRow) = "Blue" Then
    MsgBox "Found in row " & lngRow
    Exit For
    End If
    Next lngRow
    End Sub

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA Match Function (XP; SP3)

    Not sure exactly what you are after. You can do it with a worksheet function (sorry, Hans), if you do as the array formula does and create the "intermediate" range first. It is not done with a UNION as you tried, it is done with a loop.

    <pre>Sub TestTwoDimensional()
    Dim oRngProd As Range
    Dim oRngColor As Range
    Dim oJoinedRng()
    Dim oProd
    Dim oColor
    Dim lCount As Long
    Dim af As WorksheetFunction
    Dim x As Long
    Dim oResult

    Set oRngProd = Range("A2:A5")
    Set oRngColor = Range("B2:B5")
    oProd = "Two"
    oColor = "Blue"

    Set af = Application.WorksheetFunction
    lCount = oRngProd.Count
    ReDim oJoinedRng(1 To lCount)
    For x = 1 To lCount
    oJoinedRng(x) = oRngProd.Cells(x) _
    & oRngColor(x)
    Next
    oResult = af.Match(oProd & oColor, oJoinedRng, 0)
    End Sub</pre>


    In practive I would have checking to ensure that the ranges were the same size. Also I think a function may be more appropriate, but it depends on what you are after...

    For example with the function:
    <pre>Function MultiVLookup(vValue, rng As Range)
    Dim oJoinedRng()
    Dim lCount As Long
    Dim af As WorksheetFunction
    Dim x As Long

    Set af = Application.WorksheetFunction
    lCount = rng.Rows.Count
    ReDim oJoinedRng(1 To lCount)
    For x = 1 To lCount
    oJoinedRng(x) = rng.Cells(x, 1) _
    & rng.Cells(x, 2)
    Next

    MultiVLookup = rng.Cells(af.Match(vValue, oJoinedRng, 0), 3)

    End Function</pre>


    You could use something like:
    <pre>=multivlookup(E4&F4,A2:C5)</pre>


    to yield "222-Blue" from your example. this is the same results you would get from the array formula (confirm with ctrl-shift-enter):

    <pre>=INDEX(C2:C5,MATCH(E4&F4,A2:A5&B2:B5,0))</pre>


    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •