Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total by Selection by Column, Help Please.
Suppose i select a certain range, then a start a macro.
i want that macro to sum all the values for each column included in my selection and to have the results just below. suppose i have a situation like this : A B C ---------------------------- 1 3 9 7 2 5 1 8 3 1 5 3 3 2 1 1 I select range A1:C3, i start the macro, then i have the results like this ... A B C ---------------------------- 1 3 9 7 2 5 1 8 3 1 5 3 3 2 1 1 ---------------------------- 4 11 16 19 Important Note : range A1:C3 may be whatever, like F7:M26 or B9:F12, for the case F7:M26 the results will be in F27:M27 for the case B9:F12 the results will be in B13:F13 Any Help ? Best Regards. Robert. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total by Selection by Column, Help Please.
Hi Robert,
Here's one way to do it: Sub AddSUMs() Dim col As Range Dim lTopRow As Long Dim lRows As Long If Selection.Areas.Count 1 Then MsgBox "Unable to process multiple areas." Else lTopRow = Selection(1, 1).Row lRows = Selection.Rows.Count For Each col In Selection.Columns With Cells(lTopRow, col.Column) .Offset(lRows).Formula = _ "=SUM(" & .Resize(lRows, 1).Address & ")" End With Next col End If End Sub You may want to add some error handling, but this should work for you, assuming you select a rectangular range. If not, you'd have to loop through each column and find the cell below the last selected cell. BTW, I looked at the code generated by the AutoSum button in Excel. I thought there may be a corresponding VBA function, but there isn't. And the generated code was pretty ugly. <g -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Bubu wrote: Suppose i select a certain range, then a start a macro. i want that macro to sum all the values for each column included in my selection and to have the results just below. suppose i have a situation like this : A B C ---------------------------- 1 3 9 7 2 5 1 8 3 1 5 3 3 2 1 1 I select range A1:C3, i start the macro, then i have the results like this ... A B C ---------------------------- 1 3 9 7 2 5 1 8 3 1 5 3 3 2 1 1 ---------------------------- 4 11 16 19 Important Note : range A1:C3 may be whatever, like F7:M26 or B9:F12, for the case F7:M26 the results will be in F27:M27 for the case B9:F12 the results will be in B13:F13 Any Help ? Best Regards. Robert. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total by Selection by Column, Help Please.
hi babu..may be this helps you. Dim sumrow As Double Sub aaa() Sheets("Sheet1").Activate For i = Selection.Column To Selection.Columns.Count + Selection.Column - 1 For j = Selection.Row To Selection.Rows.Count + Selection.Row - 1 sumrow = sumrow + Cells(j, i) Next j Cells(Selection.Row + Selection.Rows.Count, i) = sumrow sumrow = 0 Next i End Sub ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions | |||
Total column changes colors when total equals sum of other columns | New Users to Excel | |||
disable Total and/or Sub total for a single column | Excel Worksheet Functions | |||
XL formula - total of row = total of column | Excel Worksheet Functions |