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

[Simon Lloyd]

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 sheets("Sheet1").Combobox1.value & sheets("Sheet1").Combobox2.value & sheets("Sheet1").Combobox3.valuethen 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").

All comments advice or help greatly appreciated!

[Bob Phillips]

Simon,

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

[Simon Lloyd]

here's a quick book of dummy data and explanation!

[Bob Phillips]

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

[Simon Lloyd]

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:
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 SubBTW
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.

[Simon Lloyd]

With Bob's help and direction the code that works for the above is:

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)
Rng = Range("C65536").End(xlUp).Address
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

:thumb

[artds]

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)"


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
ahmad

[Bob Phillips]

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.