Match4

Matching 4 values at the same time in 4 columns
Just like Match the function, but with 4 cells instead of 1
Searches for 4 values in 4 columns and return the row number if all found

CodeFunctionName
What is this?

Public

Tested

Original Work
Function Match4(Val1, Col1, Val2, Col2, Val3, Col3, Val4, Col4, Optional WB = "This", Optional Shee = "Active", _
Optional StartFromRow = 1)
' Searches for three cells in four columns and return the row number if all found
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
If Shee = "Active" Then Shee = ActiveSheet.Name
Match4 = 0
LastOne = MatchIf(Val1, Col1, WB, Shee, StartFromRow)
Do
If LastOne = 0 Then Exit Do
Cond1 = Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value = Val2
Cond2 = Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value = Val3
Cond3 = Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value = Val4
If TypeName(Val2) < > TypeName(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) Then _
Cond1 = CStr(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) = CStr(Val2)
If TypeName(Val3) < > TypeName(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) Then _
Cond2 = CStr(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) = CStr(Val3)
If TypeName(Val4) < > TypeName(Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value) Then _
Cond3 = CStr(Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value) = CStr(Val4)
If Cond1 And Cond2 And Cond3 Then
Match4 = Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Row
Exit Do
End If
DoEvents
LastOne = MatchIf(Val1, Col1, WB, Shee, LastOne + 1)
Loop
End Function

Val1, Col1, Val2, Col2, Val3, Col3, Val4, Col4, Optional WB, Optional Shee, Optional StartFromRow

Views 3,226

Downloads 1,310

CodeID
DB ID