MatchIfUp or MatchIfRev

Searching for an item in a column from bottom to top.
Will search for item in column starting from row defined upword (StartFromRowUP = 100)
Returns the row where we found Val1
Edit 2024-05-16: New version to accept wild card * in Val1

CodeFunctionName
What is this?

Public

Tested

Original Work
Function MatchIfRev(Val1, Col1, Optional WB = "This", Optional Shee = "Active", Optional StartFromRowUP = 100)
    MatchIfRev = MatchIfUp(Val1, Col1, WB, Shee, StartFromRowUP)
End Function
Function MatchIfUp(Val1, Col1, Optional WB = "This", Optional Shee = "Active", Optional StartFromRowUP = 100)
    '
    ' Will search for item in column starting from last row upword (StartFromRowUP)
    '
    Rett = 0
    If WB = "This" Then WB = ThisWorkbook.Name
    If WB = "Active" Then WB = ActiveWorkbook.Name
    If Shee = "Active" Then Shee = ActiveSheet.Name
    ColStart = Col1 & 1
    CoCo1 = WorksheetFunction.CountIf(Workbooks(WB).Worksheets(Shee).Range(ColStart, Col1 & StartFromRowUP), Val1)
    If CoCo1 = 0 Then GoTo ByeBye
   
    For X1 = StartFromRowUP - 1 To 1 Step -1
        If Workbooks(WB).Worksheets(Shee).Range(Col1 & X1).Value Like Val1 And Workbooks(WB).Worksheets(Shee).Range(Col1 & X1).Value > "" And Val1 > "" Then
           
            Rett = X1
            Exit For
        End If
    Next
       
ByeBye:
    MatchIfUp = Rett
End Function

Val1, Col1, Optional WB = "This", Optional Shee = "Active", Optional StartFromRowUP = 100

Views 729

Downloads 45

CodeID
DB ID