MatchIf6

Searching for row have 6 matches found in 6 different columns, because we had 5, why not 6.

CodeFunctionName

What is this?

Public

Tested

Original Work

Function MatchIf6(Val1, Col1, Val2, Col2, Val3, Col3, Val4, Col4, Val5, Col5, Val6, Col6, Optional WB = "This", Optional Shee = "Active", Optional StartFromRow = 1)
    ' Searches for six cells in six columns and return the row number if all found
    '    With number conversion possibility, like < or > or < > or <= or >=
    If WB = "This" Then WB = ThisWorkbook.Name
    If WB = "Active" Then WB = ActiveWorkbook.Name
    If Shee = "Active" Then Shee = ActiveSheet.Name
    MatchIf6 = 0
    LastOne = MatchIf(Val1, Col1, WB, Shee, StartFromRow)
    Do
        If LastOne = 0 Then Exit Do
        Cond2 = Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value Like Val2
        Cond3 = Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value Like Val3
        COnd4 = Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value Like Val4
        Cond5 = Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value Like Val5
        Cond6 = Workbooks(WB).Worksheets(Shee).Range(Col6 & LastOne).Value Like Val6
        If Left(Val2, 1) = " <" Then Cond2 = Val(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) < Val(Mid(Val2, 2))
        If Left(Val3, 1) = " <" Then Cond3 = Val(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) < Val(Mid(Val3, 2))
        If Left(Val4, 1) = " <" Then COnd4 = Val(Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value) < Val(Mid(Val4, 2))
        If Left(Val5, 1) = " <" Then Cond5 = Val(Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value) < Val(Mid(Val5, 2))
        If Left(Val6, 1) = " <" Then Cond6 = Val(Workbooks(WB).Worksheets(Shee).Range(Col6 & LastOne).Value) < Val(Mid(Val6, 2))
        If Left(Val2, 1) = " >" Then Cond2 = Val(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) > Val(Mid(Val2, 2))
        If Left(Val3, 1) = " >" Then Cond3 = Val(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) > Val(Mid(Val3, 2))
        If Left(Val4, 1) = " >" Then COnd4 = Val(Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value) > Val(Mid(Val4, 2))
        If Left(Val5, 1) = " >" Then Cond5 = Val(Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value) > Val(Mid(Val5, 2))
        If Left(Val6, 1) = " >" Then Cond6 = Val(Workbooks(WB).Worksheets(Shee).Range(Col6 & LastOne).Value) > Val(Mid(Val6, 2))
        If Left(Val2, 2) = " < >" Then Cond2 = Not Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value = Mid(Val2, 3)
        If Left(Val3, 2) = " < >" Then Cond3 = Not Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value = Mid(Val3, 3)
        If Left(Val4, 2) = " < >" Then COnd4 = Not Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value = Mid(Val4, 3)
        If Left(Val5, 2) = " < >" Then Cond5 = Not Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value = Mid(Val5, 3)
        If Left(Val6, 2) = " < >" Then Cond6 = Not Workbooks(WB).Worksheets(Shee).Range(Col6 & LastOne).Value = Mid(Val6, 3)
        If Left(Val2, 2) = " <=" Then Cond2 = Val(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) <= Val(Mid(Val2, 3))
        If Left(Val3, 2) = " <=" Then Cond3 = Val(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) <= Val(Mid(Val3, 3))
        If Left(Val4, 2) = " <=" Then COnd4 = Val(Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value) <= Val(Mid(Val4, 3))
        If Left(Val5, 2) = " <=" Then Cond5 = Val(Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value) <= Val(Mid(Val5, 3))
        If Left(Val6, 2) = " <=" Then Cond6 = Val(Workbooks(WB).Worksheets(Shee).Range(Col6 & LastOne).Value) <= Val(Mid(Val6, 3))
        If Left(Val2, 2) = " >=" Then Cond2 = Val(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) >= Val(Mid(Val2, 3))
        If Left(Val3, 2) = " >=" Then Cond3 = Val(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) >= Val(Mid(Val3, 3))
        If Left(Val4, 2) = " >=" Then COnd4 = Val(Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value) >= Val(Mid(Val4, 3))
        If Left(Val5, 2) = " >=" Then Cond5 = Val(Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value) >= Val(Mid(Val5, 3))
        If Left(Val6, 2) = " >=" Then Cond6 = Val(Workbooks(WB).Worksheets(Shee).Range(Col6 & LastOne).Value) >= Val(Mid(Val6, 3))

        If Cond2 And Cond3 And COnd4 And Cond5 And Cond6 Then
            MatchIf6 = Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Row
            Exit Do
        End If
        DoEvents
        LastOne = MatchIf(Val1, Col1, WB, Shee, LastOne + 1)
    Loop
End Function

Val1, Col1, Val2, Col2, Val3, Col3, Val4, Col4, Val5, Col5, Val6, Col6, Optional WB = "This", Optional Shee = "Active", Optional StartFromRow = 1