Multiple Criteria

[efernandes67]

Hi,

I have this formula in a cell and I need someting that returns the same result in VBA.

={MATCH(1;(Name="John")*(Type="x")*(sDate>=DATEVALUE("01-01-2011"))*(eDate<=DATEVALUE("31-12-2011"));0)}

I tried the code below, but it does not work:

Sub MultipleCriteria()
    Dim rec As Variant
    Dim myName, myType As String
    Dim sDt, eDt As String
    
    myName = "John"
    myType = "x"
    sDt = "01-01-2011"
    eDt = "31-12-2011"
       
    With Application.WorksheetFunction
        rec = .Match(1, (Range("Name") = myName) * (Range("Type") = myType) * (Range("sDate") >= DateValue(sDt)) * (Range("eDate") <= DateValue(eDt)), 0)
    End With
End Sub

Regards,
Elio Fernandes

[efernandes67]

Hi,

I solved the problem using EVALUATE formula.

Sub MultipleCriteria()
    Dim myName As String
    Dim sDt, eDt As Variant
    Dim rec As Variant
    
    myName = "John"
    myType = "x"
    sDt = Format("01-01-2011", "#")
    eDt = Format("31-12-2011", "#")
    rec = Evaluate("Match(1,(tbPlan[Name]=""" & myName & """)*(tbPlan[Type]=""" & myType & """)*(tbPlan[sDate]>=" & sDt & ")*(tbPlan[eDate]<=" & eDt & "),0)")
End Sub

Elio Fernandes