MatchIf5Up

Searches for five cells in five 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 >=

Uses MatchIfUp. Can use some work

CodeFunctionName
What is this?

Public

Tested

Original Work
Function MatchIf5Up(Val1, Col1, Val2, Col2, Val3, Col3, Val4, Col4, Val5, Col5, Optional WB = "This", Optional Shee = "Active", Optional StartFromRowUP = 100)
    ' Searches for five cells in five 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
    MatchIf5Up = 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
        Cond1 = Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value Like Val2
        Cond2 = Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value Like Val3
        Cond3 = Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value Like Val4
        COnd4 = Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value Like Val5
        Cond1 = IIf(Left(Val2, 1) = " <", Val(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) < Val(Mid(Val2, 2)), Cond1)
        Cond2 = IIf(Left(Val3, 1) = " <", Val(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) < Val(Mid(Val3, 2)), Cond2)
        If Left(Val3, 1) = " <" Then Cond2 = Val(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) < Val(Mid(Val3, 2))
        If Left(Val4, 1) = " <" Then Cond3 = Val(Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value) < Val(Mid(Val4, 2))
        If Left(Val5, 1) = " <" Then COnd4 = Val(Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value) < Val(Mid(Val5, 2))
        If Left(Val2, 1) = " >" Then Cond1 = Val(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) > Val(Mid(Val2, 2))
        If Left(Val3, 1) = " >" Then Cond2 = Val(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) > Val(Mid(Val3, 2))
        If Left(Val4, 1) = " >" Then Cond3 = Val(Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value) > Val(Mid(Val4, 2))
        If Left(Val5, 1) = " >" Then COnd4 = Val(Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value) > Val(Mid(Val5, 2))
        If Left(Val2, 2) = " < >" Then Cond1 = Not Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value = Mid(Val2, 3)
        If Left(Val3, 2) = " < >" Then Cond2 = Not Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value = Mid(Val3, 3)
        If Left(Val4, 2) = " < >" Then Cond3 = Not Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value = Mid(Val4, 3)
        If Left(Val5, 2) = " < >" Then COnd4 = Not Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value = Mid(Val5, 3)
        If Left(Val2, 2) = " <=" Then Cond1 = Val(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) <= Val(Mid(Val2, 3))
        If Left(Val3, 2) = " <=" Then Cond2 = Val(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) <= Val(Mid(Val3, 3))
        If Left(Val4, 2) = " <=" Then Cond3 = Val(Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value) <= Val(Mid(Val4, 3))
        If Left(Val5, 2) = " <=" Then COnd4 = Val(Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value) <= Val(Mid(Val5, 3))
        If Left(Val2, 2) = " >=" Then Cond1 = Val(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) >= Val(Mid(Val2, 3))
        If Left(Val3, 2) = " >=" Then Cond2 = Val(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) >= Val(Mid(Val3, 3))
        If Left(Val4, 2) = " >=" Then Cond3 = Val(Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value) >= Val(Mid(Val4, 3))
        If Left(Val5, 2) = " >=" Then COnd4 = Val(Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value) >= Val(Mid(Val5, 3))
       
        If Cond1 And Cond2 And Cond3 And COnd4 Then
            MatchIf5Up = 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, Optional WB = "This", Optional Shee = "Active", Optional StartFromRowUP = 100

Views 163

Downloads 69

CodeID
DB ID