Evaluate formula

[efernandes67]

Hi,

I have a table named 'table1' with 2 columns, 'Name' and 'sdate'

sub test_Evaluate()
    Dim rec1, rec2 as variant
    Dim myName as String
    Dim sDt as date
    
    ' This WORKS
    myName="John"
    rec1 = Sheet1.Evaluate("Match(""" & myName & """,table1[Name],0)")

    ' It DOES NOT WORK
    sDt="07-12-2011"
    rec2 = Sheet1.Evaluate("Match(""" & sDt & """,table1[sDate],0)")

End Sub

What do I need to change in the 2nd Evaluate formula to make it work?

Regards,
Elio Fernandes

[Alf]

Have you tried to dim sDt as String?

Alf

[efernandes67]

I tried with String, Date and Variant!

Elio Fernandes

[watersev]

hi Elio, A16:E16 have word "test" in on of the cells

Sub tet()
x = "test"
y = Evaluate("match(" & """" & x & """" & ",a16:e16,0)")
End Sub

[efernandes67]

Hi,

I just found a way to work with this date issue.

Sub test_Evaluate2()
    Dim rec1, rec2 As Variant
    Dim myName As String
    Dim sDt As Variant
    
    '.....

    ' IT IS WORKING NOW
    sDt = Format("11-12-2011", "#")
    rec2 = Sheet1.Evaluate("Match(" & sDt & ",table1[sDate],0)")
End Sub

Thanks for your help.
Elio Fernandes

[snb]

sDt="07-12-2011"
    rec2 = Sheet1.Evaluate("Match(""" & sDt & """,table1.columns(2),0)")

or

[c12]=format("07-12-2011","yyyy-mm-dd")
rec2=[match(c12,index(table1,,2),0)]