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.

 

[jaybird2569]

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

Thanks j_unsuitable, I was overthinking it. I knew it would have something to do with the selectCol/evalRng command. It worked perfectly.