Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sum Function is overriding Count Function in Excel VB.

Status
Not open for further replies.

cassidybklyn

Programmer
Apr 23, 2007
82
US
Hello everyone,
I am working with VB6. I have a module that counts unique items in a spreadsheet. In the same spreadsheet, I want to sum a column that has Currency.

The count function works okay, but when I try to sum the column that has the dollar value, it overrides the count. In otherwords, the xlsum function replaces the xlcount function.

How can I retain my count and at the same time sum the currency value column?

Below is my code:
With xlApp
.Selection.Subtotal GroupBy:=7, TotalList:=Array(7), PageBreaks:=True, Function:=xlCount, SummaryBelowData:=True
.Selection.Subtotal TotalList:=Array(4),Function:=xlSum, SummaryBelowData:=True
End With

Thanks.
Cassidy

 
You are missing one of the arguments:
Code:
With xlApp
  .Selection.Subtotal GroupBy:=7, TotalList:=Array(7), PageBreaks:=True, Function:=xlCount, SummaryBelowData:=True

  .Selection.Subtotal TotalList:=Array(4),Function:=xlSum, SummaryBelowData:=True, [b]Replace:=False[/b]



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Thanks. I have the "Replace" thing but I didn't include it.
Cassidy.
 
I have the "Replace" thing but I didn't include it.

I don't understand

Does this solve your problem?

If not,

where do you have the "replace thing"?
where is it not included?

I have tested this in excel and if you have "Replace:=False" on the 2nd iteration of subtoals it adds a new set rather than "replacing" the old ones...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top