Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
survey traverse computations | Excel Discussion (Misc queries) | |||
computations at odd locations | Excel Discussion (Misc queries) | |||
DATE COMPUTATIONS | Excel Worksheet Functions | |||
Date formats and computations -- Help | Excel Worksheet Functions | |||
Timing Excel computations | Excel Programming |