Adding a selection of values and storing result in a variable

[Simka]

This may have been asked before but - I regularly need to find the sum of a
(random) selection of values on a worksheet by selecting the range by and/or
holding down the SPACEBAR or CTRL key and then viewing the sum of the values
on the status bar. Idealy, I would like to press a button to start a macro to
add up all the values that have been selected and store the result in a
variable so that then this value can be pasted elsewhere. Any suggestions on
the coding?

 

[WhytheQ]

You could loop though the cells collection, of the selection in the
activesheet - whilst looping each cells value could be added to a
variable.
The above should be pretty easy: let me know if you are struggling.

Then to get the variable onto the clipboard some code like the
following might help:

Set DataObj = New MSForms.DataObject
DataObj.SetText myVariableAmount
DataObj.PutInClipboard

....you'll need to add a reference to the following library for the
above to work: Microsoft Forms 2.0 Object Library

Hope this helps
Jason.

 

[Gary''s Student]

Here is a little macro that uses Z100 as a "helper" cell:

Sub simka()
With Range("Z100")
.Value = Application.WorksheetFunction.Sum(Selection)
.Copy
End With
End Sub

You can assign the macro to a button or give it a shortcut key.

After running the macro, click on some other cell and CNTRL-v or
Edit > Paste

will depost the sum.

 

[Simka]

Many thanks that helped a treat, it's exactly wanted I was after.

PS. I should have said:

.....holding down the SHIFT or CTRL key and then.....

Simka

 

[Simka]

Continuing on and playing around, I have discovered another way by allocating
the sum of the range to a variable ready for use in further additional
calculations/functions within the code. (The ‘SumOfRange’ is the variable).

Sub Simka2()

Dim SumOfRange As Double

SumOfRange = Application.WorksheetFunction.Sum(Selection)

Debug.Print SumOfRange ‘Used to check the result in immediate window

' Additional code below
:
:
:
:
End Sub