Excel 2007 VBA Summing an array of cells

[RTK_99]

I am looking for a simple macro to take an array of numbers (from 1 to n) that are copied (Ctrl - C) and then using a short cut (say Ctrl -q) I am able to place, in a location of my choosing, the sum of the selected/copied cells.

 

[Andrew Poulsom]

No need to copy. Just select the cells and run this:

Sub Test()
    Dim x
    Dim Cell As Range
    x = WorksheetFunction.Sum(Selection)
    Set Cell = Application.InputBox(Prompt:="Choose a cell", Title:="Paste Total", Type:=8)
    Cell.Value = x
End Sub

 

[RTK_99]

Thank you. That code works! If I can be a bit more picky and ask if there is a way to substitute the pop up box with just a cell selection and a key stroke (say ctrl - q or something like that) to place the sumation value?

 

[Andrew Poulsom]

Thank you. That code works! If I can be a bit more picky and ask if there is a way to substitute the pop up box with just a cell selection and a key stroke (say ctrl - q or something like that) to place the sumation value?

No, because the code uses the Selection.