Problem using SUMPRODUCT in VBA

[jakuza]

I am using UDF function in one of my Sheets.

The part of code is:

Public Function MyBook(number As Integer, mycell As Range) As Variant

Dim MySum, SumFirst As Integer, SumExtra As String

    For Each WS In Worksheets
        If WS.CodeName = "Sheet" & number Then
            MyName = WS.Name
            Exit For
        End If
    Next WS

[B]MySum = Evaluate("=SUMPRODUCT(--('" & Sheets(MyName).Name & "'!D1:D1000=""Black""" & ");--('" & Sheets(MyName).Name & "'!J1:J1000<0);'" & Sheets(MyName).Name & "'!J1:J1000)")[/B]

SumFirst = WorksheetFunction.SumIf(Sheets(MyName).Range("D1:D1000"), ActiveSheet.Range(mycell.Address).Value, Sheets(MyName).Range("J1:J1000"))

SumExtra = " (" & MySum & ")"
MyBook = SumFirst & SumExtra

End Function

But above code give #VALUE! as a result in the cell. Can someone figure out why? I used MsgBox to test if the string in Evaluate function is good, and it seems that it is exactly as the one in worksheet that is working well. So, there must be something wrong with Evaluate function. Also, I tried with "*" instead of "--" in SUMPRODUCT but no luck.

Any help?

 

[RoryA]

Try replacing the semicolons in the formula with commas.

 

[jakuza]

No way. Again stupid semicolons... Thanks man!!!

 

[RoryA]

Glad to help.

(VBA believes that only America exists and therefore defaults to US locale settings for almost everything unless you specify otherwise, where you can)