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
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 = 100)
' Returns the row where we found Val1
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 = Val1 Then
Rett = X1
Exit For
End If
Next
ByeBye:
MatchIfUp = Rett
End Function
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 = 100)
' Returns the row where we found Val1
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 = 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 544
Downloads 24
CodeID
DB ID