Add (Sum) multiple cells in a column and display results in message box

[jaybird2569]

Good afternoon,


I am just beginning with VBA and use this site quite often for guidance, now I need to post a question of my own. I have a worksheet with multiple columns where I need to know the sum of mixed cells. I would like the macro to start off with a message box instructing to select a column to perform the function then after selecting a column heading (B, C, D, etc) the macro would complete four separate functions and provide the results in a message box. An example would be after the prompt to select a column, user selects column C, the macro adds cells 3 & 6 for one total, cells 4 & 7 for another total, cells 5 & 8 for another total and cell 10 for another total then provides all 4 totals in one message box. The cells I would be adding would always be the same, only the user selected column would change.

 

[shg]

Welcome to the board.

Select the column first, then run (perhaps via a keyboard shortcut),

Sub OddSums()
  With ActiveWindow.RangeSelection(1).EntireColumn
    .Range("A3, A6, A4, A7, A6, A8, A10").Select
    MsgBox Replace("@3 + @6 = " & .Cells(3).Value2 + .Cells(6).Value2 & vbLf & _
                   "@4 + @7 = " & .Cells(4).Value2 + .Cells(7).Value2 & vbLf & _
                   "@6 + @8 = " & .Cells(6).Value2 + .Cells(8).Value2 & vbLf & _
                   "@10 = " & .Cells(10).Value2, "@", Split(.Address(False, False), ":")(0))
  End With
End Sub

 

[j_unsuitable]

Or something like this?

Sub sumcells()
Dim selectCol As String
Dim SumA As Long, SumB As Long, SumC As Long, sumTotal As Long

selectCol = InputBox("Select Column (letter):")
SumA = Cells(3, selectCol).Value + Cells(3, selectCol)
SumB = Cells(4, selectCol).Value + Cells(7, selectCol)
SumC = Cells(5, selectCol).Value + Cells(8, selectCol)

MsgBox (SumA & ";" & SumB & ";" & SumC & ";" & cells(10,selectCol).value )
End Sub

Julian

 

[jaybird2569]

Welcome to the board.

Select the column first, then run (perhaps via a keyboard shortcut),

Sub OddSums()
  With ActiveWindow.RangeSelection(1).EntireColumn
    .Range("A3, A6, A4, A7, A6, A8, A10").Select
    MsgBox Replace("@3 + @6 = " & .Cells(3).Value2 + .Cells(6).Value2 & vbLf & _
                   "@4 + @7 = " & .Cells(4).Value2 + .Cells(7).Value2 & vbLf & _
                   "@6 + @8 = " & .Cells(6).Value2 + .Cells(8).Value2 & vbLf & _
                   "@10 = " & .Cells(10).Value2, "@", Split(.Address(False, False), ":")(0))
  End With
End Sub

Thanks for the quick reply. It worked perfect. I was just looking for the totals, not all of the calculations in the message box, but I'm sure I could edit that.

 

[jaybird2569]

Or something like this?

Sub sumcells()
Dim selectCol As String
Dim SumA As Long, SumB As Long, SumC As Long, sumTotal As Long

selectCol = InputBox("Select Column (letter):")
SumA = Cells(3, selectCol).Value + Cells(3, selectCol)
SumB = Cells(4, selectCol).Value + Cells(7, selectCol)
SumC = Cells(5, selectCol).Value + Cells(8, selectCol)

MsgBox (SumA & ";" & SumB & ";" & SumC & ";" & cells(10,selectCol).value )
End Sub

Julian

Julian, this is just what I was looking for. Thank you.

 

[shg]

You're welcome.

 

[jaybird2569]

Julian, Would there be a way to click the column heading instead of entering the column letter via keystroke?

 

[j_unsuitable]

Uh i am pretty bad at this active/selected cell stuff, but i can give it a whirl and maybe my questions will clear up some stuff and shg will come back in:

will the person be clicking on the header (e.g the header in cell C1) or will they click on the "C" which highlights the entire column "C"

and

will the macro run when the person clicks on the cell/column or will they click there and then run the macro seperately by clicking a button or using a key combination?

shg's solutions is pretty dependent on selected cells already, so i hope he gets back here and offers some input

 

[jaybird2569]

Uh i am pretty bad at this active/selected cell stuff, but i can give it a whirl and maybe my questions will clear up some stuff and shg will come back in:

will the person be clicking on the header (e.g the header in cell C1) or will they click on the "C" which highlights the entire column "C"

and

will the macro run when the person clicks on the cell/column or will they click there and then run the macro seperately by clicking a button or using a key combination?

shg's solutions is pretty dependent on selected cells already, so i hope he gets back here and offers some input

The user would be clicking on "C" to highlight the entire column. My goal would be to have this macro assigned to a button which the user would click then an Application.InputBox would ask for a column to run the calculation (user would click on "C") then a message box would pop up with the 4 totals. I have a macro for the application.inputbox but can't figure out how to use the selected range as the selected column from Julian's code.
Here is the code for the input box

Sub ProductionTest()
'Input box to select a range


Dim evalRng As Range
Dim msg As String


msg = "Select the column to calculate"
On Error Resume Next
Set evalRng = Application.InputBox(msg, Type:=8)
If Err.Number <> 0 Then Exit Sub 'Cancel was clicked or a valid range wasn't selected.
On Error GoTo 0
Application.ScreenUpdating = False


End Sub

and I would like to use that result in place of the line

selectCol = InputBox("Select Column (letter):")

Again, thanks to everyone who chips in.

 

[j_unsuitable]

See if this works the way you want it to:

Sub ProductionTest()
'Input box to select a range
Dim evalRng As Range
Dim msg As String
Dim SumA As Long, SumB As Long, SumC As Long

msg = "Select the column to calculate"
On Error Resume Next
Set evalRng = Application.InputBox(msg, Type:=8)
If Err.Number <> 0 Then Exit Sub 'Cancel was clicked or a valid range wasn't selected.
On Error GoTo 0

SumA = Cells(3, evalRng.Column).Value + Cells(6, evalRng.Column)
SumB = Cells(4, evalRng.Column).Value + Cells(7, evalRng.Column)
SumC = Cells(5, evalRng.Column).Value + Cells(8, evalRng.Column)

MsgBox (SumA & ";" & SumB & ";" & SumC & ";" & Cells(10, evalRng.Column).Value)

End Sub