Editing in formula

A

Anna

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$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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top