Consulting

Results 1 to 8 of 8

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

  1. #1
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location

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

    All comments advice or help greatly appreciated!
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    Simon,

    Do you have a sample workbook, I am struggling to envisage it.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    here's a quick book of dummy data and explanation!
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    [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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    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
    Quote 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.
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  6. #6
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    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)
    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

    [/VBA]
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  7. #7
    VBAX Regular
    Joined
    Nov 2007
    Posts
    28
    Location
    Quote 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
    ahmad

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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