Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

where i do the change

  Asked By: Jawna    Date: Oct 30    Category: MS Office    Views: 755
  

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
#VALUE. I copy that formula from site
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

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Jimmie Ramirez     Answered On: Oct 30

You would need to change

Function SumIf3D(Range3D As String, Criteria As String, _
Optional Sum_Range As Variant) As Variant

To

Function SumIf3D(Range3D As String, Criteria As String, _
Optional Sum_Range As Variant,Optional WkBkFileName As Variant) As
Variant

For it to be useful you would also need to make use of the WkBkFileName later
on in the code.

 
Didn't find what you were looking for? Find more on where i do the change Or get search suggestion and latest updates.




Tagged: