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?

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.




On 11 Sep, 13:32, Simka <Si...@discussions.microsoft.com> wrote:
> 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?

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.
--
Gary''s Student - gsnu200804


"Simka" wrote:

> 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?

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


"Gary''s Student" wrote:

> 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.
> --
> Gary''s Student - gsnu200804
>
>
> "Simka" wrote:
>
> > 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?

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




"Simka" wrote:

> 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
>
>
> "Gary''s Student" wrote:
>
> > 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.
> > --
> > Gary''s Student - gsnu200804
> >
> >
> > "Simka" wrote:
> >
> > > 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?