Creating a macro to SUM a varying range of cells

**Earmite** · 11-05-2010, 03:57 PM

First: Hello all, greetings!

Second: I am completely worthless when it comes to programming, so it was much simpler to just ask this here. I tried several variations on some automatically generated macros, but none of them worked.

Now: I need a macro to create a SUM formula based on selected cells, and it needs to appear in a single cell relative to the cells I selected. Here's some screens demonstrating what I need:
1: Select some cells
http://img251.imageshack.us/img251/9981/38594808.jpg

2: SUM them up in that cell there
http://img249.imageshack.us/img249/6193/85762684.jpg

3: Done
http://img20.imageshack.us/img20/7238/19859476.jpg

Thank you!

**tony h** · 11-05-2010, 04:01 PM

It would help to know how you are using the spreadsheet. ie what is the spreadsheet doing.

**mikerickson** · 11-05-2010, 04:06 PM

Perhaps something like

Sub aMacro()
    On Error GoTo CancelExit:
    With Application.InputBox("Select the Cells to sum", Type:=8)
         .Offset(.Rows.Count - 1, .Columns.Count).Cells(1, 1).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
    End With
CancelExit:
    On Error GoTo 0
End Sub

**arthurbr** · 11-05-2010, 04:12 PM

Why post an image when we need a sheet?

**Earmite** · 11-05-2010, 04:17 PM

Ah sorry. I forgot that that would be the most logical. Here's a sample sheet wtihout all my sensitive information on it:

EDIT: Mike, I tried your thing, and it worked perfectly. A little editing of it, and I figured out exactly what I needed! Thanks again!

**Earmite** · 11-05-2010, 04:42 PM

If anybody cares, here's the final macro I'll be using:

Sub aMacro2()
    On Error GoTo CancelExit:
    With Selection
         .Offset(.Rows.Count - 1, .Columns.Count).Cells(1, 1).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
    End With
CancelExit:
    On Error GoTo 0
End Sub

Thanks mikerickson!