microsoft.public.excel.programming

Jericho

12/19/2006 2:06:00 AM

You can't pass a 4th argument to a function that expects only 3.
Joerg

"Anna" <colleen1980@gmail.com> wrote in message
news:1166493032.141585.199970@80g2000cwy.googlegroups.com...
> Hi: Can any one please tell me how to i modify the sumif3D code so that
> it will accept the file name too. I try it to pass 4th argument in it
> but it shows no error and no value. I copied this formula from
> http://www.j-walk.com/ss/excel/eee/...
>
> =SumIf3D("SALE0106:SALE1206!A2:A9999",A2,J$2:J$9999,"blabla.xls")
>
> Function SumIf3D(Range3D As String, Criteria As String, _
> Optional Sum_Range As Variant) As Variant
>
> Dim sTestRange As String
> Dim sSumRange As String
> Dim Sheet1 As Integer
> Dim Sheet2 As Integer
> Dim n As Integer
> Dim Sum As Double
>
> ' Application.Volatile
>
> If Parse3DRange(Application.Caller.Parent.Parent.Name, _
> Range3D, Sheet1, Sheet2, sTestRange) = False Then
> SumIf3D = CVErr(xlErrRef)
> End If
>
> If IsMissing(Sum_Range) Then
> sSumRange = sTestRange
> Else
> sSumRange = Sum_Range.Address
> End If
>
> Sum = 0
> For n = Sheet1 To Sheet2
> With Worksheets(n)
> Sum = Sum + Application.WorksheetFunction.SumIf(.Range _
> (sTestRange), Criteria, .Range(sSumRange))
> End With
> Next n
> SumIf3D = Sum
> End Function
>
> Thanks.
>