Application.Match Error while comparing 2 arrays taken from a sheet range-CodePudding

• What the code is supposed to do: It should take 2 ranges from the sheet as arrays and use Application.Match to display the row number where the value match is in Range #1. In this case it should display 10 and 12.

Sub RenglonesdeMatch()
    Dim i As Integer, z, v3 As Variant
    Dim Piso As Variant
    Dim RowNum As Integer
    With Sheets("Hoja1")
  
        
        Piso = .Range("D10:D13").Value 'RANGE #1
        v3 = .Range("H3:H6").Value 

        For i = 1 To UBound(Piso, 1)
      
            z = Application.Match(v3, Piso, 0)
            If Not IsError(z) Then
     
                RowNum = Cells((i   9), 4).Row 
               
                Debug.Print RowNum
            End If
        Next i
    End With
End Sub

• This is my sheet's data:

H3:H6 MARIA ADRIANA JUAN LILIANA

D10:D13 JUAN FER MARIA JIMENA

• The problem: It displays all rows for the whole Range (10, 11, 12, 13), even if there's no match. Also, I'm not sure if this line is correct...

z = Application.Match(v3, Piso, 0)

I tried changing it to this but the code says there's Error 9.

 z = Application.Match(v3(i), Piso, 0)

• NOTE: Both arrays will always have different sizes but in this case I'm using the same size.

I'd appreciate any help. Thank you in advance!

CodePudding user response：

Application.Match is able to compare two arrays, returning an array of matched element positions. Erroring (only) for not matched cases (elements)... For instance, in your case:

  z = Application.Match(v3, Piso, 0)

will return an array as: 3, Error 2042, 1, Error 2042

In order to make it visible you may try:

  z = Application.IfError(Application.match(V3, Piso, 0), "x")
  Debug.Print Join(Application.Transpose(z), "|")
  'it will show: 3|x|1|x

So, isError does not return true and in all iteration cases the matching line returns the same...

Now, I suppose that you do not want returning the matched positions, even if you did not answer mai clarification question...

So, most probably, you will need something like:

 For i = 1 To UBound(Piso)
            z = Application.match(V3(i, 1), Piso, 0)
            If Not IsError(z) Then
                RowNum = i   9: Debug.Print RowNum
            End If
 Next i

But this is debatable and depends on what you really try accomplishing and is your turn to clarify this aspect...

CodePudding user response：

Return Matching Rows Using `Application.Match`

Note that Application.Match is faster with ranges than with arrays.

Option Explicit

' Source              Destination
' Index Row  Value    Index Row Value
'   1   D10  Juan       1   H3  Maria
'   2   D11  Fer        2   H4  Adriana
'   3   D12  Maria      3   H5  Juan
'   4   D13  Jimena     4   H6  Liliana

Sub RenglonesdeMatch()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet: Set ws = wb.Worksheets("Hoja1")
    
    Dim srg As Range: Set srg = ws.Range("D10:D13")
    Dim shRow As Long: shRow = srg.Row - 1
    
    Dim drg As Range: Set drg = ws.Range("H3:H6")
    Dim dhRow As Long: dhRow = drg.Row - 1
    
    ' The matches of source in destination: if it's not an error, then
    ' it was found in destination at the resulting index i.e. the n-th
    ' cell of the destination range.
    Dim sMatchData() As Variant: sMatchData = Application.Match(srg, drg, 0)
    
    ' Contents of sMatchData:
    ' sMatchData(1, 1) = 3 ' index of 'Juan' in destination
    ' sMatchData(2, 1) = Error 2042 ' 'Fer' not found
    ' sMatchData(3, 1) = 1 ' index of 'Maria' in destination
    ' sMatchData(4, 1) = Error 2042 ' 'Jimena' not found
    
    Debug.Print "sIndex", "sRow", "sValue", "dIndex", "dRow", "dValue"
    
    Dim sr As Long
    Dim dr As Variant ' could be an error
    
    For sr = 1 To UBound(sMatchData, 1)
        dr = sMatchData(sr, 1)
        If IsNumeric(dr) Then
            Debug.Print sr, shRow   sr, srg.Cells(sr).Value, _
                dr, dhRow   dr, drg.Cells(dr).Value
        End If
    Next sr
    
' Result in the Immediate window ('Ctrl G'). 'sRow' is the requirement.
'
' sIndex        sRow          sValue        dIndex        dRow          dValue
'  1             10           Juan           3             5            Juan
'  3             12           Maria          1             3            Maria
    
End Sub

Return Matching Rows Using Application.Match

Return Matching Rows Using `Application.Match`