Formula needed with a changing denominator for each grouping
 

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

Formula needed with a changing denominator for each grouping
 

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" wrote in
message ...
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

Formula needed with a changing denominator for each grouping
 

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

"Tom Ogilvy" wrote:

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" wrote in
message ...
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

Formula needed with a changing denominator for each grouping
 

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.

--
Regards,
Tom Ogilvy


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

"Tom Ogilvy" wrote:

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" wrote in
message ...
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