computations at odd but specified locations
 

Hi

Apologies to those who are seeing it again. Since there was no
response on excel.misc group I'm posting here.

I have data available as follows:

Segment Value Percentage
A 2
B 7
C 5
Total
X 3
Y 2
Total
Q 4
W 6
E 3
R 8
Total


Unforrtunately, as you can see, the rows are unequal. I need to put
totals in the cell adjacent to where "Total" occurs. Also, based on
the totals percentage break-ups for each group needs to be computed.
Can someone suggest a way out?

computations at odd but specified locations
 

this may do what you want, just adjust the sheet name, column and starting row.

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim x As Long
Dim sTotal As Double

Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
x = 2
Do While x <= lastrow
With ws
Do While .Range("A" & x).Value < "Total"
sTotal = sTotal + .Range("B" & x).Value
x = x + 1
Loop
.Range("B" & x).Value = sTotal
x = x + 1
End With
sTotal = 0
Loop
End Sub

--


Gary Keramidas
Excel 2003


"Utkarsh" wrote in message
...
Hi

Apologies to those who are seeing it again. Since there was no
response on excel.misc group I'm posting here.

I have data available as follows:

Segment Value Percentage
A 2
B 7
C 5
Total
X 3
Y 2
Total
Q 4
W 6
E 3
R 8
Total


Unforrtunately, as you can see, the rows are unequal. I need to put
totals in the cell adjacent to where "Total" occurs. Also, based on
the totals percentage break-ups for each group needs to be computed.
Can someone suggest a way out?

computations at odd but specified locations
 

if the number of rows of data is not the same, you probably need a macro to
insert the formula, you should read the start of line for each group and go
to the empty cell to put a formula of the sum.

how many rows of mix of data ? is there any case that only happen one line
of data for single group?


"Utkarsh" wrote:

Hi

Apologies to those who are seeing it again. Since there was no
response on excel.misc group I'm posting here.

I have data available as follows:

Segment Value Percentage
A 2
B 7
C 5
Total
X 3
Y 2
Total
Q 4
W 6
E 3
R 8
Total


Unforrtunately, as you can see, the rows are unequal. I need to put
totals in the cell adjacent to where "Total" occurs. Also, based on
the totals percentage break-ups for each group needs to be computed.
Can someone suggest a way out?
.

computations at odd but specified locations
 

One way. I presumed Segment in column A, Value in column B, Percentage
in column C.

Sub Sumtest()
Dim tmp As Range, Total As Range, sTotal As Range, rng As Range
Dim i As Long

Set Total = Columns("B").SpecialCells(xlCellTypeConstants, 1)

For i = 1 To Total.Areas.Count
Set tmp = Total.Areas(i)
tmp.Offset(, 1).NumberFormat = "0.0%"
Set sTotal = tmp.Resize(1).Cells(tmp.Cells.Count + 1)
sTotal.Formula = "=sum(" & tmp.Address(False, False) & ")"
For Each rng In tmp
rng.Offset(, 1).Formula = "=" & rng.Address(False, False) & _
"/" & sTotal.Address(False, False)
Next
Next
End Sub

Keiji

Utkarsh wrote:
Hi

Apologies to those who are seeing it again. Since there was no
response on excel.misc group I'm posting here.

I have data available as follows:

Segment Value Percentage
A 2
B 7
C 5
Total
X 3
Y 2
Total
Q 4
W 6
E 3
R 8
Total


Unforrtunately, as you can see, the rows are unequal. I need to put
totals in the cell adjacent to where "Total" occurs. Also, based on
the totals percentage break-ups for each group needs to be computed.
Can someone suggest a way out?