MatchIf6Up

Searches for 6 values in 6 columns and return the row number found in.
Searching is from bottom cell to up until row 1
Starting from StartFromRowUP.

CodeFunctionName

What is this?

Public

Tested

Original Work

Function MatchIf6Up(Val1, Col1, Val2, Col2, Val3, Col3, Val4, Col4, Val5, Col5, Val6, Col6, Optional WB = "This", Optional Shee = "Active", Optional StartFromRowUP = 100)
    ' Searches for six cells in six columns and return the row number if all found
    '    From bottom of range to up, starting from row StartFromRowUP
    '    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
    MatchIf6Up = 0
    LastOne = MatchIfUp(Val1, Col1, WB, Shee, StartFromRowUP)
'    If CoCo1 = 0 Then Exit Function
    For X1 = StartFromRowUP - 1 To 1 Step -1
        If LastOne = 0 Then Exit For
        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
        Cond2 = IIf(Left(Val2, 1) = " <", Val(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) < Val(Mid(Val2, 2)), Cond2)
        Cond3 = IIf(Left(Val3, 1) = " <", Val(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) < Val(Mid(Val3, 2)), Cond3)
        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))
        Cond6 = IIf(Left(Val6, 1) = " <", Val(Workbooks(WB).Worksheets(Shee).Range(Col6 & LastOne).Value) < Val(Mid(Val6, 2)), Cond6)
        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
            MatchIf6Up = LastOne ' Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Row
            Exit For
        End If
        DoEvents
        LastOne = MatchIfUp(Val1, Col1, WB, Shee, LastOne)
    Next
End Function

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