Summing random cells as you click on them

[Ben in CA]

I need to create a "program" with an Excel document that allows me to click a
button like "start adding" and then I can click on random cells (well, they
wouldn't actually be random, but not in any series of columns or rows), and
the contained values to be copied to successive cells in an empty column on
the side, that I can then have a Sum setup up on.

Basically, I have different pieces of equipment listed in an Excel
spreadsheet, along with various options. (Several hundred) I want to be able
to select which ones (even just by clicking on the price/value), and
automatically add them together - currently this is done on paper. (The costs
of the various options, etc. are scattered about, and aren't all in a
particular row or column.)

Once I'm done calculating it, probably I'd need to have a "Stop adding"
button that I could press and then it would stop calculating the value.

Preferably it only copies the actual number in the cells to the new
location, as many of the cells are themselves calculated by formulas.

Can anyone think of a way of doing this? I've been looking around for a way
to do this, but I haven't found anything that will do it yet.

Thanks in advance to anyone who solves this problem!

 

[Per Jessen]

Hi

Insert a button from the "control Toolbox" menu on your sheet, then
right click on the button and select "View code". Paste the code below
into the code window. Change the TargetCell as desired and return to
the worksheet. Click "Exit design mode"

Private Sub CommandButton1_Click()
TargetCell = "H2" ' Change to suit
Range(TargetCell, Range(TargetCell).End(xlDown)).ClearContents
For Each cell In Selection
Range(TargetCell).Offset(off, 0) = cell.Value
MyResult = MyResult + cell.Value
off = off + 1
Next
Range(TargetCell).Offset(off, 0) = MyResult
End Sub

Now you can click the first cell to add, then hold down "CTRL" while
clicking further cells to add. Click on your button when you are done
and watch the result.

Hopes it helps.

Regards,
Per

 

[Dave Peterson]

Maybe you could just ask the user to select the range in the macro and then
cycle through the selection:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim oWks As Worksheet
Dim oRow As Long

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(prompt:="Select a range", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub 'user hit cancel
End If

Set oWks = Workbooks.Add(1).Worksheets(1)

oRow = 0
For Each myCell In myRng.Cells
oRow = oRow + 1
oWks.Cells(oRow, "A").Value = myCell.Address(external:=True)
With oWks.Cells(oRow, "B")
.NumberFormat = myCell.NumberFormat
.Value = myCell.Value
End With
Next myCell

With oWks
.Cells(oRow + 1, "B").FormulaR1C1 = "=sum(r1c2:r[-1]c)"
.UsedRange.Columns.AutoFit
End With

End Sub

 

[Shane Devenshire]

Hi,

Before we get started creating a fancy macro to do this, see if this is good
enough:

Click the first cell with numbers, hold down the Ctrl key and click any
other cell with numbers. Observe the right side of the Status Bar. Most
likely you will see SUM = 123,456 or some number. If you don't see SUM then
right click on the right side of the status bar and from the pop-up choose
SUM, noting you can do Average, Count, Max and Min.

You can highlight any cells you want and you will see the calcualtion. You
can highlight by dragging the mouse, doing Shift+Click, using Ctrl+Click or
any other method of the 250 ways you can select.

If this helps, please click the Yes button,

Cheers,
Shane Devenshire

 

[Ben in CA]

Thank you for all the answers so far!

I'll try them and see if they'll work.

Ben

 

[Ben in CA]

Per's solution works great!

Thank you to Dave, Shane, and Steve as well - but I think for now Per's idea
will do what I need.

Have a great day!