MatchIf (aka VMatchIf)

The best Match I created, return 0 if not found, kind of CountIF+Match
Vertical matches for a value in a column

CodeFunctionName

What is this?

Public

Tested

Original Work

Function MatchIf(Val1, Col1, Optional Wb = "This", Optional Shee = "Active", Optional StartFromRow = 1)
MatchIf=VMatchIf(Val1, Col1, Wb , Shee , StartFromRow )
End Function
Function VMatchIf(Val1, Col1, Optional Wb = "This", Optional Shee = "Active", Optional StartFromRow = 1)
If Wb = "This" Then Wb = ThisWorkbook.Name
If Wb = "Active" Then Wb = ActiveWorkbook.Name
If Shee = "Active" Then Shee = ActiveSheet.Name
VMatchIf = 0
Col1End = Col1 & Workbooks(Wb).Worksheets(Shee).Range("A1").EntireColumn.Rows.Count - 1
CoCo1 = WorksheetFunction.CountIf(Workbooks(Wb).Worksheets(Shee).Range(Col1 & StartFromRow, Col1End), Val1)
If CoCo1 = 0 Then Exit Function
On Error Resume Next
Err.Clear
LastOne = WorksheetFunction.Match(Val1, Workbooks(Wb).Worksheets(Shee).Range(Col1 & StartFromRow, Col1End), 0) + StartFromRow - 1
If Err.Number = 0 Then GoTo GotIt
Err.Clear
LastOne = WorksheetFunction.Match(Val(Val1), Workbooks(Wb).Worksheets(Shee).Range(Col1 & StartFromRow, Col1End), 0) + StartFromRow - 1
If Err.Number = 0 Then GoTo GotIt
Err.Clear
LastOne = WorksheetFunction.Match(CStr(Val1), Workbooks(Wb).Worksheets(Shee).Range(Col1 & StartFromRow, Col1End), 0) + StartFromRow - 1
If Err.Number = 0 Then GoTo GotIt
Err.Clear
On Error GoTo 0
Exit Function
GotIt:
Err.Clear
On Error GoTo 0
VMatchIf = LastOne
End Function

Val1, Col1, Optional Wb, Optional Shee, Optional StartFromRow