computations at odd but specified locations

[Utkarsh]

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?

 

[Gary Keramidas]

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

 

[Leung]

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?

 

[keiji kounoike]

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