CODEWITHSUNDEEP
excelexcel-2010vba
Andy5
Andy5

Reputation: 2405

Excel VBA 2010 - worksheet.subtotal function sum and average in the same row

I am creating a macro which takes data from an Oracle database, which means that the number of rows can vary depending upon the result. In this macro, I am using the worksheet.subtotal function, which sums up on a number of columns when the group changes. This all works, but the problem that I am facing are that a few of the columns need to be average.

Searching around I have not been able to find a solution where you can have both average and sum in the same row. I do not wish to have a situation where the user has to manually intervene to change each column for each group.

Is there away where you can have both average and sum in the same row?

Upvotes: 2

Views: 1340

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149325

AFAIK you cannot use both i.e Sum and Average together in Subtotal but don't loose heart :) Here is an alternative.

Let's say you have two columns. In one you want Sum and the other you want Average. In your code, set Sum for both columns. When the report is generated, simply highlight the column where you want Average and simply find and replace subtotal(9 with subtotal(1 using CTRL + H If you don't want to do it manually then you can do the Find and Replace in code as well ;)

This would work as Sum and Average have the same structure.

=Sum(<Range>)
=Average(<Range>)

Upvotes: 1

Related Questions