Sum Function is overriding Count Function in Excel VB.

[cassidybklyn]

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

 

[xlbo]

You are missing one of the arguments:

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

 

[cassidybklyn]

Thanks. I have the "Replace" thing but I didn't include it.
Cassidy.

 

[xlbo]

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