Using DSUM function with variable "Field", etc

[Guest]

I am trying to use this function to enter values into a large grid of cells in a worksheet

For each cell, the "Field" is variable dependent on column position, and criteria is variable dependent on row position

Can DSum contain variables for these elements? The Help indicates text or a column number only for "Field"

Or can I only enter formulae into the actual cells?

 

[Guest]

The answer is yes
The field number could be substituted with a MATCH function that returns the position of known heading within your range of headings
Have a look at Match - and pay particular attention to its final argument as this determines the answer in a big way !!

Martin

 

[Guest]

Thanks for this very quick response. What I did not make clear (sorry!) in my initial request was that it is the VBA (not Worksheet) DSum function I am trying to use. Does your reply still hold good?

 

[Tom Ogilvy]

There is no VBA Dsum function. Do you mean you are using the worksheet Dsum
function in VBA

WorksheetFunction.Dsum

or

Application.Dsum

if so, then the answer should be applicable since it is the same function.

--
Regards,
Tom Ogilvy

Thanks for this very quick response. What I did not make clear (sorry!)

in my initial request was that it is the VBA (not Worksheet) DSum function I
am trying to use. Does your reply still hold good?

 

[Guest]

Yu
You can use MATCH within VBA in the same way - E

Col_Pos = Application.Match(Range("My Heading"), Range("A1:K1"), 0

which dumps into the variable Col_Pos the position number that "My Heading" is first found in the range A1:K1
Be aware that if your range is, say, from C1:M1, MATCH returns the position of the entry IN THE RANGE - ie NOT the column heading. In this example you'll need to subtract 2 from the result to get the column heading - but this shouldn't affect you as you are using DSUM where the field number is relative to the range and not the actual column numbe

regard

Martin

 

[Guest]

Whoops
Sorry - I replaced a range reference just before posting my reply and forgot to take out the Range bit
Here are two correct choices...

Col_Pos = Application.Match("My Heading", Range("A1:K1"), 0

Col_Pos = Application.Match(Range("G6").Value, Range("A1:K1"), 0

regard

Martin

 

[Guest]

Thanks, To

You are correct - as you can tell, I am a novice in this area. I knew what I meant, but didn't express myself correctly

I discovered the (inevitable) blooper of starting the column variable with zero instead of 1!