VBA Code for Summing Data Above total until a blank row

[Aggie2014]

Hi All,

I'm working on a project to automate a manual process currently in place and am struggling with one piece of the macro puzzle.

I have a spreadsheet with data across several columns and rows, separated by a blank row with totals underneath each data category. The totals are generated by the software from which the data is pulled and are static, so if any of the lines above them are changed they will not update. Each Total row has a specific name, ex: ***TOTAL PERSONNEL SERVICES***. The asterisks are part of the data dump so unfortunately I can't change them. The number of rows will vary with each dataset but the columns will remain the same.

What I'm looking for is code to add a sum formula for each of the categories that will capture all of the data above the total up until the blank row separator. I included an example of the format with some fake data below to help clarify how the data is laid out. I have more categories than the two below but didn't want to clutter the post.

ACOUNT	DESCRIPTION	FY18 YE Actual	FY18 YTD Actual	Budget	FY19 YTD Actual	FY18/19 YTD Difference	Projection	Over/under	Proposed	proposed to mod
PERSONNEL SERVICES
60000​	Salaries	50​	60​	100​	20​	-40​	100​	20​	200​	9​
60001​	FICA	50​	60​	100​	20​	-40​	100​	20​	200​	9​
60002​	Insurance	50​	60​	100​	20​	-40​	100​	20​	200​	9​
60003​	WC	50​	60​	100​	20​	-40​	100​	20​	200​	9​
	***TOTAL PERSONNEL SERVICES***	200​	240​	400​	80​	-160​	400​	80​	800​	36​
SUPPLIES
60010​	Office Supplies	50​	60​	100​	20​	-40​	100​	20​	200​	9​
60020​	Office Furnishings	50​	60​	100​	20​	-40​	100​	20​	200​	9​
60030​	Motor Parts	50​	60​	100​	20​	-40​	100​	20​	200​	9​
60040​	Other Supplies	50​	60​	100​	20​	-40​	100​	20​	200​	9​
	***TOTAL SUPPLIES***	200​	240​	400​	80​	-160​	400​	80​	800​	36​

Thanks for all of your help on this!

 

[Fluff]

How about

Sub Aggie2014()
    Dim Rng As Range
    
    For Each Rng In Range("C2:C" & Rows.Count).SpecialCells(xlConstants).Areas
        Rng.Offset(Rng.Count - 1).Resize(1, 9).Formula = "=sum(" & Rng.Resize(Rng.Count - 1).Address(0, 0) & ")"
    Next Rng
End Sub

 

[Aggie2014]

That is perfect! Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Aggie2014]

I tested this code in a test spreadsheet and it worked perfectly. However when I add it to my larger macro, I receive a Run-Time error 1004 for the range offset line

I adjusted the coordinates to fit my spreadsheet and am not sure why it's not liking it. Here is the new code:

Dim Rng as Range
For Each Rng in Range("D8:D" & Rows.Count).SpecialCells(xlConstants).Areas
Rng.Offset(Rng.Count -1).Resize(1, 9).Formula = "=sum"(" & Rng.Resize(Rng.Count - 1).Address(0, 0) & ")"
Next Rng

I do have a hidden row A in the file if that would make a difference.

Any suggestions?

 

[Fluff]

You have got an extra set of quotes here that need to be removed.

"=sum"("

Also is your header row in row 7, or row 8?

 

[Aggie2014]

Got it. It works now. Thanks!

 

[Fluff]

Glad you sorted it & thanks for the feedback