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
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
' 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 187
Downloads 69
CodeID
DB ID
![](/Assets/img/_load903.gif)