Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop that finds blanks, then subtotals values into different column
Hi there,
0 rows are my header rows, and 1 rows are individual order items. I need a loop that will find the blank cells in column E and then subtotal the values into column C of its header row. A B C D E 0 B100010 1 B100010 101303 1 82.92 1 B100010 101305 1 117.31 1 B100010 101307 1 90.02 0 B310003 1 B310003 215013 1 12.33 1 B310003 301503 1 10.75 0 B20055 1 B20055 303009 1 17.77 1 B20055 303011 1 25.67 1 B20055 303013 1 27.15 1 B20055 217001 1 31.55 I know it is basic stuff, but I need some help (I'm an excel VBA virgin!!) I am ok with the looping bit but can't think it through. I am running out of time and the will to live !! .... Anyone??? Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop that finds blanks, then subtotals values into different column
Hi,
subtotal of what? empty cells you can find using this: dim emptycells as range emptycells=intersect(range("e:e"),activesheet.used range).SpecialCells(xlCellTypeBlanks) Regards, Ivan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop that finds blanks, then subtotals values into different column
subtotal of values in Column E - i.e. the total of each order. Cheers,
Bevy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop that finds blanks, then subtotals values into different column
Hi,
you can use something like this: intersect(range("e:e"),activesheet.usedrange).Spec ialCells(xlCellTypeBlanks).offset(0,1).formular1c1 ="=SUMIF(" & intersect(range("a:a"),activesheet.usedrange).Addr ess(referencestyle:=xlR1C1) & "," range("a1").Address(referencestyle:=xlR1C1,relativ eto:=range("e1"),rowabsolute:=false,columnabsolute :=false) & "," & intersect(range("e:e"),activesheet.usedrange).Addr ess(referencestyle:=xlR1C1) Regards, Ivan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop that finds blanks, then subtotals values into different colum
Sub PutInTotals()
Dim rng as Range Dim cell as Range Dim rng1 as Range set rng = Range(Cells(rows.count,"E"),Cells(rows.count,"E"). End(xlup)) for each cell in rng if isempty(cell) and not isempty(cell.offset(1,0)) then if isempty(cell.offset(2,0)) then cell.Formula = "=Sum(" & cell.Offset(1,0).Address(0,0) & ")" else set rng1 = range(cell.offset(1,0),cell.offset(1,0).End(xldown )) cell.Formula = "=Sum(" & rng1.Address(0,0) & ")" end if End if Next end sub -- Regards, Tom Ogilvy "Bevy" wrote: Hi there, 0 rows are my header rows, and 1 rows are individual order items. I need a loop that will find the blank cells in column E and then subtotal the values into column C of its header row. A B C D E 0 B100010 1 B100010 101303 1 82.92 1 B100010 101305 1 117.31 1 B100010 101307 1 90.02 0 B310003 1 B310003 215013 1 12.33 1 B310003 301503 1 10.75 0 B20055 1 B20055 303009 1 17.77 1 B20055 303011 1 25.67 1 B20055 303013 1 27.15 1 B20055 217001 1 31.55 I know it is basic stuff, but I need some help (I'm an excel VBA virgin!!) I am ok with the looping bit but can't think it through. I am running out of time and the will to live !! .... Anyone??? Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count values in array of data where no blanks in one column | Excel Worksheet Functions | |||
Subtotals of Items in A Column based on Spec. Values found in Colu | Excel Discussion (Misc queries) | |||
Counting multiple values (including blanks) in one column | Excel Discussion (Misc queries) | |||
Create a macro that finds values then sorts | Excel Worksheet Functions | |||
Trying to select values in a column with blanks | Excel Programming |