Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSUM - Modifying the function...
DSUM - Modifying the function...
I am not sure if everyone knows the DSUM function but I am trying to modify this so the criteria and the field names are in two separate ranges. The Syntax of the DSUM is DSUM( table , column , criteria ) where the table is the range of all data elements, column is the field you will like to sum and the criteria is the range (including the field names in the table) you will like to define for the criteria. Criteria is where I want to change the function so the label and the Criteria is in two different ranges. In order to define the criteria you will first have to have the label and under the label you must define your criteria. Well - here is my question, Is there anyway I can combine the two arrays to one array and send that in to the DSUM function? This is the function I am trying to create. Function CSUM(ByVal MyDB As Variant, ByVal MyFNToSum As Variant, ByVal MyFields As Variant, ByVal MyCriteria As Variant) As Variant 'MyDB is the range where allof the data and fields exist 'MyFNToSum is the field name you will like to sum 'MyFields is the range where the field names exist 'MyCriteria is the array that includes tthe criteria for the sum function. Dim MyRange As Excel.Range MyRange = Excel.Union(MyFields, MyCriteria) MyRange = MyCriteria CSUM = Application.WorksheetFunction.DSum(MyDB, MyFNToSum, MyRange) End Function No matter what I do I keep getting "#VALUE" in my excel sheet cell. It seems like that I touch the variable excel does not like it. I thought this was an array but it looks like an Excel.Range object. However, that still did not work. ANYONE - HELP!!!!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSUM - Modifying the function...
A possible alternative is to use sumproduct. Assuming your table is in A1:B8
and your criteria is in F3, and the values you want summed are in column B: =SUMPRODUCT(--(A2:A8=F3),B2:B8) Even though you unioned the two ranges, they are still non-contiguous. It appears DSUM does not work if the criteria range is non-contiguous and , if the DSUM function (called from an excel worksheet) doesn't work w/a non-contigous range, there's no reason to expect it should if called via VBA. "DKIM" wrote: DSUM - Modifying the function... I am not sure if everyone knows the DSUM function but I am trying to modify this so the criteria and the field names are in two separate ranges. The Syntax of the DSUM is DSUM( table , column , criteria ) where the table is the range of all data elements, column is the field you will like to sum and the criteria is the range (including the field names in the table) you will like to define for the criteria. Criteria is where I want to change the function so the label and the Criteria is in two different ranges. In order to define the criteria you will first have to have the label and under the label you must define your criteria. Well - here is my question, Is there anyway I can combine the two arrays to one array and send that in to the DSUM function? This is the function I am trying to create. Function CSUM(ByVal MyDB As Variant, ByVal MyFNToSum As Variant, ByVal MyFields As Variant, ByVal MyCriteria As Variant) As Variant 'MyDB is the range where allof the data and fields exist 'MyFNToSum is the field name you will like to sum 'MyFields is the range where the field names exist 'MyCriteria is the array that includes tthe criteria for the sum function. Dim MyRange As Excel.Range MyRange = Excel.Union(MyFields, MyCriteria) MyRange = MyCriteria CSUM = Application.WorksheetFunction.DSum(MyDB, MyFNToSum, MyRange) End Function No matter what I do I keep getting "#VALUE" in my excel sheet cell. It seems like that I touch the variable excel does not like it. I thought this was an array but it looks like an Excel.Range object. However, that still did not work. ANYONE - HELP!!!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DSUM function | New Users to Excel | |||
DSUM function | New Users to Excel | |||
Using the function DSUM | Excel Discussion (Misc queries) | |||
DSUM - Modifying the function... | Excel Worksheet Functions | |||
DSum function | Excel Worksheet Functions |