Match multi criteria

[dnk42]

Hello, can anyone tell me why my code does not work?

Sub Cross()
    
    Dim nombre As Range
    Dim rut As Range
    Dim marca As Range
    Dim modelo As Range
    Dim patente As Range
    Dim motor As Range
    Dim row As Integer


    
    
    With Sheets("DTT")
        Set nombre = Range("A4:A186")
        Set rut = Range("C4:C186")
        Set marca = Range("F4:F186")
        Set modelo = Range("G4:G186")
        Set patente = Range("J4:J186")
        Set motor = Range("K4:K186")
    End With
    
    Sheets("Nomina").Activate
    
    
    Do While ActiveCell.Value = ""
        row = ActiveCell.row


        ActiveCell.Value = Application.WorksheetFunction.Index(Sheets("DTT").Range("patente"), Application.WorksheetFunction.Match(Cells(row, 1) & Cells(row, 6) & Cells(row, 8) & Cells(row, 9), Sheets("DTT").Range("nombre") & Sheets("DTT").Range("rut") & Sheets("DTT").Range("marca") & Sheets("DTT").Range("modelo"), 0))
        ActiveCell.Offset(0, 1).Value = Application.WorksheetFunction.Index(Sheets("DTT").Range("motor"), Application.WorksheetFunction.Match(Cells(row, 1) & Cells(row, 6) & Cells(row, 8) & Cells(row, 9), Sheets("DTT").Range("nombre") & Sheets("DTT").Range("rut") & Sheets("DTT").Range("marca") & Sheets("DTT").Range("modelo"), 0))
        ActiveCell.Offset(1, -1).Activate
    Loop
End Sub

 

[offthelip]

Re: help with match multi criteria

the Match function state that the second parameter must be an array:

Lookup_array - a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.

Your syntax for the second parameter doesn't look like an array to me, :

Sheets("DTT").Range("nombre") & Sheets("DTT").Range("rut") & Sheets("DTT").Range("marca") & Sheets("DTT").Range("modelo"

Using index and Match and looping through the cells is a very poor and slow way of doing what you want to do.
You should try to avoid setting variables to ranges unless it is absolutely necessary it is much faster and easier to load the data into variant arrays. If you start looping through a few thousand rows of data by addressing it as ranges excel vba gets very slow
A much better way of doing this is to load all the data you need into variant arrays, then loop through the data and check for the match by a simple equality and use the index when it matches to pick the correct data to output
It is not clear to me exactly what you are trying to do, but I have tried to rewrite your code in the way that I would do this, using variant arrays and VBA to find the “matches” which is much easier to debug than using excel functions.

Sub test()
   With Sheets("DTT")
   ' load all the dat from DDTT sheet into variant arrays fro checking
         nombre = Range("A4:A186")
         rut = Range("C4:C186")
         marca = Range("F4:F186")
         modelo = Range("G4:G186")
         patente = Range("J4:J186")
         Motor = Range("K4:K186")
    End With
    
    Sheets("Nomina").Activate
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    ' load all the data from Nomina sheet
    inarr = Range(Cells(1, 1), Cells(lastrow, 9))
    ' initialise and output arry
    Range(Cells(1, 10), Cells(lastrow, 11)) = ""
    outarr = Range(Cells(1, 10), Cells(lastrow, 11))
         For irow = 1 To lastrow
            ' search for match between col 1 and Nombre, col 6 and rut , ext
           ' loop through the match data
             For j = 1 To 186
            If (inarr(irow, 1) = nombre(j, 1)) & (inarr(irow, 6) = rut(j, 1)) & (inarr(irow, 8) = marca(j, 1)) & (inarr(irow, 9) = modelo(j, 1)) Then
                outarr(irow, 1) = patente(j, 1)
                outarr(irow, 2) = Motor(j, 1)
                Exit For
            End If
          Next irow
                 
    Range(Cells(1, 10), Cells(lastrow, 11)) = outarr
                 
End Sub

It is not clear where you want the output so I have written it out to columns 10 and 11 (J and K)

 

[dnk42]

Re: help with match multi criteria

the Match function state that the second parameter must be an array:

Your syntax for the second parameter doesn't look like an array to me, :

Sheets("DTT").Range("nombre") & Sheets("DTT").Range("rut") & Sheets("DTT").Range("marca") & Sheets("DTT").Range("modelo"

Using index and Match and looping through the cells is a very poor and slow way of doing what you want to do.
You should try to avoid setting variables to ranges unless it is absolutely necessary it is much faster and easier to load the data into variant arrays. If you start looping through a few thousand rows of data by addressing it as ranges excel vba gets very slow
A much better way of doing this is to load all the data you need into variant arrays, then loop through the data and check for the match by a simple equality and use the index when it matches to pick the correct data to output
It is not clear to me exactly what you are trying to do, but I have tried to rewrite your code in the way that I would do this, using variant arrays and VBA to find the “matches” which is much easier to debug than using excel functions.

Sub test()
   With Sheets("DTT")
   ' load all the dat from DDTT sheet into variant arrays fro checking
         nombre = Range("A4:A186")
         rut = Range("C4:C186")
         marca = Range("F4:F186")
         modelo = Range("G4:G186")
         patente = Range("J4:J186")
         Motor = Range("K4:K186")
    End With
    
    Sheets("Nomina").Activate
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    ' load all the data from Nomina sheet
    inarr = Range(Cells(1, 1), Cells(lastrow, 9))
    ' initialise and output arry
    Range(Cells(1, 10), Cells(lastrow, 11)) = ""
    outarr = Range(Cells(1, 10), Cells(lastrow, 11))
         For irow = 1 To lastrow
            ' search for match between col 1 and Nombre, col 6 and rut , ext
           ' loop through the match data
             For j = 1 To 186
            If (inarr(irow, 1) = nombre(j, 1)) & (inarr(irow, 6) = rut(j, 1)) & (inarr(irow, 8) = marca(j, 1)) & (inarr(irow, 9) = modelo(j, 1)) Then
                outarr(irow, 1) = patente(j, 1)
                outarr(irow, 2) = Motor(j, 1)
                Exit For
            End If
          Next irow
                 
    Range(Cells(1, 10), Cells(lastrow, 11)) = outarr
                 
End Sub

It is not clear where you want the output so I have written it out to columns 10 and 11 (J and K)

i couldn't run the code. but i get what you're getting at. i'll try it out. Thanks!