Folks,
    I have a problem of using isMissing to check if an optional range is missing. In every case, the result of isMissing function returns not missing and the result of the calculation is incorrect. Below is the code. Please help.
   Thanks in advance.
----
Function myCal(m As Range, Optional r As Range) As integer

    If IsMissing(r) = True Then
        myCal = WorksheetFunction.Sum(m.Rows.Value)
    Else
        myCal = WorksheetFunction.SumProduct(m.Rows.Value, r.Rows.Value)
    End If
End Function

[Edited by justarrive on 20-10-2005 at 06:08 PM GMT]

Hi,
Looks like the check should be "r is nothing". I don't know why, but Excel is passing nothing into the "r" parameter.
Try the following:

Public Function myCal(m As Range, Optional r As Range) As Integer     If r Is Nothing Then         myCal = WorksheetFunction.Sum(m.Rows.Value)     Else         myCal = WorksheetFunction.SumProduct(m.Rows.Value, r.Rows.Value)     End If End Function

Hope this helps,
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

totally works. thanks so much.

Hi JS,
Can you mark this as solved if it is pls?
Ta
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)