Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with a formula I am editing | Excel Discussion (Misc queries) | |||
Formula editing | Excel Worksheet Functions | |||
Editing a formula | Excel Discussion (Misc queries) | |||
Help with editing a formula | Excel Worksheet Functions | |||
Formula Editing | Excel Worksheet Functions |