I have a series of columns with sums at the top of each using the SUM()
function. I want to run the subtotal command across them from VBA using range
("a1")= Worksheetfunction.subtotal(5, Range). But I keep getting back a zero
for the result although the columns have numbers in them. Is this due to a
flaw with trying to use the SUM() Function or have I gone Crazy.

Jeff

Hi
what is your exact code and how have you defined 'range'

--
Regards
Frank Kabel
Frankfurt, Germany

jeff wrote:
I have a series of columns with sums at the top of each using the
SUM() function. I want to run the subtotal command across them from
VBA using range ("a1")= Worksheetfunction.subtotal(5, Range). But I
keep getting back a zero for the result although the columns have
numbers in them. Is this due to a flaw with trying to use the SUM()
Function or have I gone Crazy.

Jeff

Subtotal(5,range) says to return the Minumum value in the range. Is one of
the the cells being subtotalled returning a zero?

--
Regards,
Tom Ogilvy

"jeff" wrote in message
...
I have a series of columns with sums at the top of each using the SUM()
function. I want to run the subtotal command across them from VBA using
range
("a1")= Worksheetfunction.subtotal(5, Range). But I keep getting back a
zero
for the result although the columns have numbers in them. Is this due to a
flaw with trying to use the SUM() Function or have I gone Crazy.

Jeff

Neither. You probably pasted your "numbers" in from somewhere else (a
web site?) and they were entered as Text (same thing happens if you
enter the numbers with the cells preformatted as Text). SUM() ignores
text.

To check, copy an empty cell, select your numbers. Choose Edit/Paste
Special, selecting the Values and Add radio buttons. Click OK.

This will coerce Text numbers to actual number values.



In article ,
"jeff" wrote:

I have a series of columns with sums at the top of each using the SUM()
function. I want to run the subtotal command across them from VBA using range
("a1")= Worksheetfunction.subtotal(5, Range). But I keep getting back a zero
for the result although the columns have numbers in them. Is this due to a
flaw with trying to use the SUM() Function or have I gone Crazy.

Well having a SUM() or any other formula in the Range should not affect
the subtotal.

It might be possible that one the SUM() is returning "0"
or the range you specified in Subtotal includes a cell haveing value "0"

Sharad



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I've tried using the following:
xlApp.Range("g28") = xlApp.WorksheetFunction.Subtotal(5,
xlApp.Workbooks("Release Plan
(1,2,3,4).xls").Worksheets(ShtRef).Range(myAddr))

Then I went to the specific sheet and did a subtotal locally and still got
the same answer.

Also I've discovered that Excel 2002 help is prone to errors, it says:
Index Meaning
1 Automatic
2 Sum
3 Count
4 Average
5 Max
6 Min
7 Product
8 Count Nums
9 StdDev
10 StdDevp
11 Var
12 Varp

When in Fact "4" RETURNS the highest value
Jeff

"Sharad" wrote:

Well having a SUM() or any other formula in the Range should not affect
the subtotal.

It might be possible that one the SUM() is returning "0"
or the range you specified in Subtotal includes a cell haveing value "0"

Sharad



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

This is what it actually says:

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Not sure where you are looking.

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
I've tried using the following:
xlApp.Range("g28") = xlApp.WorksheetFunction.Subtotal(5,
xlApp.Workbooks("Release Plan
(1,2,3,4).xls").Worksheets(ShtRef).Range(myAddr))

Then I went to the specific sheet and did a subtotal locally and still got
the same answer.

Also I've discovered that Excel 2002 help is prone to errors, it says:
Index Meaning
1 Automatic
2 Sum
3 Count
4 Average
5 Max
6 Min
7 Product
8 Count Nums
9 StdDev
10 StdDevp
11 Var
12 Varp

When in Fact "4" RETURNS the highest value
Jeff

"Sharad" wrote:

Well having a SUM() or any other formula in the Range should not affect
the subtotal.

It might be possible that one the SUM() is returning "0"
or the range you specified in Subtotal includes a cell haveing value "0"

Sharad



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!