# Thread: Solved: How to find (match) 3 criteria on a row?

1. ## Solved: How to find (match) 3 criteria on a row?

Gents, i need to find 3 consecutive items or criteria on a row using the values of thre worksheet comboboxes, for arguments sake lets say i have a column with days of the week (i dont but for data's sake assume i have) repeated many times (not contiguos) in the next column i may have a repeating set of numbers i.e 1,2,3,4, etc to a maximum of 20 (not contiguos) in the third column i may have numbers 1234 ,1245,etc (not contiguos)i want to search and match lets say [vba]sheets("Sheet1").Combobox1.value & sheets("Sheet1").Combobox2.value & sheets("Sheet1").Combobox3.value[/vba]then when all 3 are found contiguos in the same ROW i want to paste to the same row starting at a column whose title is found in lets say sheets("Sheet1").Range("A1").  Reply With Quote

2. Simon,

Do you have a sample workbook, I am struggling to envisage it.  Reply With Quote

3. here's a quick book of dummy data and explanation!  Reply With Quote

4. [vba]

Public Sub SimonsJob()
Dim mpRow As Long
Dim mpLastRow As Long
Dim mpFormula As String
Dim mpColumn As Long
Dim mpTarget As Worksheet

With Worksheets("Sheet1")

Set mpTarget = Worksheets(.ComboBox1.Value)
mpLastRow = mpTarget.Cells(Rows.Count, "A").End(xlUp).Row
mpFormula = "MATCH(1,(" & .ComboBox1.Value & "!A1:A" & mpLastRow & "=""" & .ComboBox2.Value & """)*" & _
"(" & .ComboBox1.Value & "!B1:B" & mpLastRow & "=" & .ComboBox3.Value & ")*" & _
"(" & .ComboBox1.Value & "!C1:C" & mpLastRow & "=" & .ComboBox4.Value & "),0)"
On Error Resume Next
mpRow = .Evaluate(mpFormula)
On Error GoTo 0
If mpRow > 0 Then

On Error Resume Next
mpColumn = Application.Match(.Range("b1").Value, Worksheets(.ComboBox1.Value).Rows(1), 0)
On Error GoTo 0
If mpColumn > 0 Then

mpTarget.Cells(mpRow, mpColumn).Value = mpTarget.Cells(mpRow, 1).Value
mpTarget.Cells(mpRow + 1, mpColumn).Value = mpTarget.Cells(mpRow, 2).Value
mpTarget.Cells(mpRow + 2, mpColumn).Value = mpTarget.Cells(mpRow, 3).Value
End If
End If
End With
End Sub
[/vba]  Reply With Quote

5. Bob, thanks for the reply, i tried application.Match but couldn't manage it...however the code you supplied works in the dummy workbook to a degree, it pastes the value of the comboboxes down in the desired column where it should have pasted the list "One, Two, Three...etc" transposed to that location, also in my workbook it failed to find the matches but are clearly there, i modified your code slightly:
[VBA]Public Sub SimonsJob()
Dim mpRow As Long
Dim mpLastRow As Long
Dim mpFormula As String
Dim mpColumn As Long
Dim mpTarget As Worksheet

With Activeworksheet

Set mpTarget = Worksheets(ActiveSheet.ComboBox1.Value)
mpLastRow = mpTarget.Cells(Rows.Count, "A").End(xlUp).Row
mpFormula = "MATCH(1,(" & ActiveSheet.ComboBox1.Value & "!A1:A" & mpLastRow & "=""" & ActiveSheet.ComboBox2.Value & """)*" & _
"(" & ActiveSheet.ComboBox1.Value & "!B1:B" & mpLastRow & "=" & ActiveSheet.ComboBox3.Value & ")*" & _
"(" & ActiveSheet.ComboBox1.Value & "!C1:C" & mpLastRow & "=" & ActiveSheet.ComboBox4.Value & "),0)"
On Error Resume Next
MsgBox mpFormula
mpRow = .Evaluate(mpFormula)
On Error GoTo 0
If mpRow > 0 Then

On Error Resume Next
mpColumn = Application.Match(ActiveSheet.Range("A1").Value, Worksheets(ActiveSheet.ComboBox1.Value).Rows(1), 0)
On Error GoTo 0
If mpColumn > 0 Then

mpTarget.Cells(mpRow, mpColumn).Value = mpTarget.Cells(mpRow, 1).Value
mpTarget.Cells(mpRow + 1, mpColumn).Value = mpTarget.Cells(mpRow, 2).Value
mpTarget.Cells(mpRow + 2, mpColumn).Value = mpTarget.Cells(mpRow, 3).Value
End If
End If
End With
End Sub[/VBA]BTW Originally Posted by Xld
Public Sub SimonsJob()
nice touch! .
You will see in your code i added a msgbox to show the match formula to check that it was looking for the right criteria...it was!

I have sent you a mail with the workbook to your gmail account.  Reply With Quote

6. With Bob's help and direction the code that works for the above is:
[VBA]
Public Sub Activity()
Dim mpRow As Long
Dim mpLastRow As Long
Dim mpFormula As String
Dim mpColumn As Long
Dim mpTarget As Worksheet
Dim Rng As String
Dim UdRange As Range
Dim wsSheet As Worksheet
With ActiveSheet
Set mpTarget = Worksheets(.ComboBox1.Value)
mpLastRow = mpTarget.Cells(Rows.Count, "A").End(xlUp).Row
mpFormula = "MATCH(1,('" & .ComboBox1.Value & "'!A1:A" & mpLastRow & "=""" & .ComboBox2.Value & """)*" & _
"('" & .ComboBox1.Value & "'!B1:B" & mpLastRow & "=""" & .ComboBox3.Value & """)*" & _
"('" & .ComboBox1.Value & "'!C1:C" & mpLastRow & "=" & .ComboBox4.Value & "),0)"
On Error Resume Next
mpRow = .Evaluate(mpFormula)
On Error GoTo 0
If mpRow <= 0 Then Exit Sub
If mpRow > 0 Then
On Error Resume Next
mpColumn = Application.Match(ActiveSheet.Range("A1").Value, Worksheets(.ComboBox1.Value).Rows(1), 0)
On Error GoTo 0
If mpColumn > 0 Then
Set mpPaste = mpTarget.Cells(mpRow, mpColumn).Offset(0, 1)
Set UdRange = ActiveSheet.Range("C2:" & Rng)
UdRange.Copy
mpPaste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
End If
End If
End With
MsgBox "Data Update Complete", vbOKOnly, "Transfer Confirmation"
For Each wsSheet In Worksheets
wsSheet.Visible = wsSheet.Name = "Activity Sheet"
Next wsSheet
End Sub

[/VBA]   Reply With Quote

7. Originally Posted by Simon Lloyd
[vba]

mpFormula = "MATCH(1,(" & ActiveSheet.ComboBox1.Value & "!A1:A" & mpLastRow & "=""" & ActiveSheet.ComboBox2.Value & """)*" & _
"(" & ActiveSheet.ComboBox1.Value & "!B1:B" & mpLastRow & "=" & ActiveSheet.ComboBox3.Value & ")*" & _
"(" & ActiveSheet.ComboBox1.Value & "!C1:C" & mpLastRow & "=" & ActiveSheet.ComboBox4.Value & "),0)"

[/vba]
erm.... this question might sound stupid, but i'm gonna ask anyway. Why do you use '*' in between each criteria? Why not use the comma(,) or '&' sign. And how do you know when to use '*' instead of ',' or '&'?

regards  Reply With Quote

8. It is the way a conditional match works, the * is AND the two conditions and + Ors it. Using , is no good, that is a statement separator, and & concatenates the two conditions.

It is nothig to do with VBA, and all to do with how Excel works.

ACtually, by asking the question I experimented a bit, and found that
=MATCH("TRUETRUE",(A1:A6="a")&(B1:B6=1),0)
also works, but not as elegant. I will play with this some more.  Reply With Quote

#### Posting Permissions

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