Editing in formula
 

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/eee003.txt

=SumIf3D("SALE0106:SALE1206!A2:A9999",A2,J$2:J$999 9,"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.

Editing in formula
 

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

"Anna" wrote in message
ups.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/eee003.txt

=SumIf3D("SALE0106:SALE1206!A2:A9999",A2,J$2:J$999 9,"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.