Filling Formulas Down within Variable-Length Sub-Totals

[BenLee]

I have a variable-length data table that I format, sort, and sub-total with VB. I almost have this to completion, but I can't figure out the final VB steps and have been resorting to manual entry thus-far.
Within each separate sub-total group I need to caculate a weighted average using each calcluated sub-total. Each sub-total will vary in length, but the sub-total names remain constant day-to-day.
The data always starts in A4 with these column headings:
Skillset (name), Group (name), CallsAnswered (count), AvgAnsweredDelay (duration), and WeightedAvgAnsweredDelay. Each Skillset fits in a Group and the Group column is the sub-total by. WeightedAvgAnsweredDelay is a sub-totaled column totaling to zero and is where I need to input the formula: =(C5/$C$9)*D5 or =(Count/TotalCount)*Duration. The next line (E6) would be =(C6/$C$9)*D6 progressing on until the sub-total for that Group is reached and then re-starting at the next sub-total Group.
The $C$9 could vary from day-to-day as a Skillset could be absent or more could be added. These variations then "roll-down" throughout each sub-total changing the starting and finishing point of each.
I've thought about other options, but I need to account for new Skillsets being added without my knowledge which prevents the use of a template Skillsets list. I've spent days on this specific issue with little progress and I'm hoping there is something easy that I'm missing. This forum has been instrumental in getting me this far, along with a Bill "MrExcel" Jelen book. I'm looking to put this issue to rest and move on to the next challenge. Thanks!

 

[Gerald Higgins]

Hi Ben, and welcome to the board.

I'm not sure, but a pivot table might help you here.

 

[RoryA]

That can certainly be added in. Can you post the code you have?

 

[BenLee]

Thanks Gerald!
I have considered a PivotTable, but I'm looking to keep this data in a single worksheet so users have a single point to go and drill-down for more detailed data.
I forgot to mention, I'm running XP SP2, Excel 2007. The worksheet is below. I shortened it as the original has more sub-totals and additional columns. Once I have this formula figured out I'll be using it in another column on another WeightedAvg (AbandonDelay).<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[BenLee]

I can certainly post the code if you feel it would help, but it's fairly lengthy (despite some cleaning) and it only formats and sub-totals a messy report output. The worksheet I just posted is the cleaned and almost finished result (although it is only part of the full worksheet). From this worksheet I summarize the data further (automatically) separated by market.
Ideally, I'd like to avoid the PivotTable route as I'd be starting from scratch to get all the drill-down data in a single worksheet.
Let me know if you'd like to see the code.