SUM() function not behaving

[Guest]

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

 

[Frank Kabel]

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

 

[Tom Ogilvy]

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

 

[JE McGimpsey]

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.

 

[Sharad]

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

 

[Guest]

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

 

[Tom Ogilvy]

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.