Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Column in a Selected Range of Columns
Hi,
Using VBA code, how could I select a bunch of columns that may or may not be continuous, and return a sum (or any function) from each column? For example, I select Columns A,B, D, and E. The procedure (maybe in a msgbox) returns: A total = 100 B total = 200 D total = 150 E total = 175 Thanks for your help. -Rob p.s. The specific problem I am having is returning which column I selected. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Column in a Selected Range of Columns
Sub ShowTotals()
Dim rng As Range, ar As Range Dim col As Range, sStr as String Set rng = Selection.EntireColumn For Each ar In rng For Each col In ar sStr = sStr & Left(col.Address(0, 0), 1 - (col.Column 26)) sStr = sStr & " total = " & Format(Application.Sum(col), "#,###.00") sStr = sStr & vbNewLine Next Next MsgBox sStr End Sub -- Regards, Tom Ogilvy "Rob G" wrote in message ... Hi, Using VBA code, how could I select a bunch of columns that may or may not be continuous, and return a sum (or any function) from each column? For example, I select Columns A,B, D, and E. The procedure (maybe in a msgbox) returns: A total = 100 B total = 200 D total = 150 E total = 175 Thanks for your help. -Rob p.s. The specific problem I am having is returning which column I selected. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Column in a Selected Range of Columns
That is just perfect!
Thank you! "Tom Ogilvy" wrote in message ... Sub ShowTotals() Dim rng As Range, ar As Range Dim col As Range, sStr as String Set rng = Selection.EntireColumn For Each ar In rng For Each col In ar sStr = sStr & Left(col.Address(0, 0), 1 - (col.Column 26)) sStr = sStr & " total = " & Format(Application.Sum(col), "#,###.00") sStr = sStr & vbNewLine Next Next MsgBox sStr End Sub -- Regards, Tom Ogilvy "Rob G" wrote in message ... Hi, Using VBA code, how could I select a bunch of columns that may or may not be continuous, and return a sum (or any function) from each column? For example, I select Columns A,B, D, and E. The procedure (maybe in a msgbox) returns: A total = 100 B total = 200 D total = 150 E total = 175 Thanks for your help. -Rob p.s. The specific problem I am having is returning which column I selected. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a Column in a Selected Range of Columns
Actually, that should have been:
Sub ShowTotals() Dim rng As Range, ar As Range Dim col As Range Set rng = Intersect(Cells, Selection.EntireColumn) For Each ar In rng.Areas For Each col In ar.Columns sStr = sStr & Left(col.Address(0, 0), 1 - (col.Column 26)) sStr = sStr & " total = " & Format(Application.Sum(col), "#,###.00") sStr = sStr & vbNewLine Next Next MsgBox sStr End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Sub ShowTotals() Dim rng As Range, ar As Range Dim col As Range, sStr as String Set rng = Selection.EntireColumn For Each ar In rng For Each col In ar sStr = sStr & Left(col.Address(0, 0), 1 - (col.Column 26)) sStr = sStr & " total = " & Format(Application.Sum(col), "#,###.00") sStr = sStr & vbNewLine Next Next MsgBox sStr End Sub -- Regards, Tom Ogilvy "Rob G" wrote in message ... Hi, Using VBA code, how could I select a bunch of columns that may or may not be continuous, and return a sum (or any function) from each column? For example, I select Columns A,B, D, and E. The procedure (maybe in a msgbox) returns: A total = 100 B total = 200 D total = 150 E total = 175 Thanks for your help. -Rob p.s. The specific problem I am having is returning which column I selected. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change color to selected columns in a stacked column chart | Charts and Charting in Excel | |||
Multiple cells or columns are selected instead of selected cell or | Excel Discussion (Misc queries) | |||
referencing selected cells | Excel Worksheet Functions | |||
Choose data from a selected range and put result in new column | Excel Worksheet Functions | |||
Referencing a range of columns and rows with the IF function | Excel Worksheet Functions |