Match multi criteria

dnk42

New Member
Joined
Jan 29, 2018
Messages
2
Hello, can anyone tell me why my code does not work?

Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,081
Office Version
2010
Platform
Windows
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, :
Code:
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.

Code:
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)
 
Last edited:

dnk42

New Member
Joined
Jan 29, 2018
Messages
2
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, :
Code:
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.

Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,090,040
Messages
5,412,005
Members
403,407
Latest member
craigey1

This Week's Hot Topics

Top