Formula needed with a changing denominator for each grouping

[MissyLovesExcel]

I have numerous groupings of the following information. I'm trying to create
a formula or macro that will calculate the allocation (part/whole) by
automatically updating the denominator based on where the next blank line is,
signifying the next group, and subtracting one (to where the "over all" line
is). The next group would automatically use the new denominator. I don't
want to have to manually change the equation for each group.

EQUITY $310,481 98%
FIXED INCOME $- 0%
CASH EQUIVALENTS $5,644 2%
CASH $- 0%
OVER ALL $316,125 100%

EQUITY $1,153,192
FIXED INCOME $871,634
CASH EQUIVALENTS $22,972
CASH $3,598
OVER ALL $2,051,396

 

[Tom Ogilvy]

Sub AddPercentages()
Dim rng as range, ar as Range
set rng = columns(2).specialCells(xlConstants,xlNumbers)
for each ar in rng.areas
ar.offset(0,1).formula= "=" & ar(1).Address(0,1) & _
"/" & ar(ar.count).address(1,1)
ar.offset(0,1).Numberformat = "0%"
Next
End Sub

 

[MissyLovesExcel]

Okay, I get a VBA error "400" is all it says when I try to run it. Any
thoughts?

 

[Tom Ogilvy]

It ran fine for me.

Try closing excel and re-opening. I have had that error box pop up before
(on other occasions). It seems to be more an internal excel problem and
not related to anything in the code.