MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Range question for DSUM, DCOUNT, etc...

  Asked By: Jose    Date: Aug 15    Category: MS Office    Views: 1706

Is it possible to programmatically create a Range, for use in database
functions, from two non-continuous groups of cells (or sub-ranges if
you will)?

The catch is that I don't want to create the range in my workbook, I
just need it to exist in memory so I can pass it through the built-in
Excel database functionality.



4 Answers Found

Answer #1    Answered By: Martha Gonzalez     Answered On: Aug 15

: Is it possible to programmatically  create a Range, for use
: in database  functions, from two non-continuous groups  of
cells  (or sub-ranges if you will)?
: The catch  is that I don't want to create  the range  in my
: workbook, I just need it to exist  in memory  so I can pass
: it through the built-in Excel database functionality.

You can dim a variable as a Range and use all the
properties and methods available for a range object. Type
the following, place your cursor inside the word "Range",
press the F1 key and select "Range Collection" under the See
Also link. The information you need is probably under the
Union section.

Dim rngMyRange As Range

Answer #2    Answered By: Poppy Brown     Answered On: Aug 15

Maybe you have something else in mind...

But this is what I tried with no luck:

Sub Macro3()
Dim RngOut As Range

Set RngOut = Application.Union(Sheets("QueryRanges").Range
("D2:F2"), Sheets("QueryRanges").Range("D6:F6"))

MsgBox Application.WorksheetFunction.DSum(Sheets("Data2").Range
("A1:DE30063"), Sheets("Data2").Range("CP1"), RngOut)

End Sub

I got an "unable to get DSum property of worksheetfunction class"

If I try to select a cell and paste RngOut I get an error that says
something about function not available with multiple selections.

Am I missing something?

Answer #3    Answered By: Juanita Mason     Answered On: Aug 15

I think the problem is not that you do not have a range, but that DSUM only
works on a list or database. That is "a list of related data in which rows of
related information are records, and columns of data are fields. The first row
of the list contains labels for each column."

The only workaround I can think of would be to copy the data to a new sheet -
making sure it met the criteria for a database, do the DSUM, and then hide, or
delete the sheet.

Or, possibly, do separate DSUMs on each of the parts of the list and add them

Answer #4    Answered By: Khadeeja Malik     Answered On: Aug 15

I thought about that, but I figured there might be a way to
hold the range  in memory  only and then feed that range to DSUM.
The OP only needs to create  the criteria on the fly, so we can't
do separate runs, but a temporary spreadsheet to hold the criteria
would work.

Does a more knowledgeable person know if it is possible to
create a range in a VBA variable that can be fed to a function
without first creating the range on a spreadsheet?

Didn't find what you were looking for? Find more on Range question for DSUM, DCOUNT, etc... Or get search suggestion and latest updates.