Matching three values at the same time in three columns
Just like Match the function, but with three cells instead of 1
Searches for three values in three columns and return the row number if all found.
Update 2020-06-23: Fix an issue in Cond2
Function Match3(Val1, Col1, Val2, Col2, Val3, Col3, Optional WB = "This", Optional Shee = "Active", _
Optional StartFromRow = 1)
' Searches for three cells in three 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
Match3 = 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
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 Cond1 And Cond2 Then
Match3 = Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Row
Exit Do
End If
DoEvents
LastOne = MatchIf(Val1, Col1, WB, Shee, LastOne + 1)
Loop
End Function
Optional StartFromRow = 1)
' Searches for three cells in three 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
Match3 = 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
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 Cond1 And Cond2 Then
Match3 = 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, Optional WB, Optional Shee, Optional StartFromRow
Views 3,250
Downloads 1,269
CodeID
DB ID