Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a selection of values and storing result in a variable
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a selection of values and storing result in a variable
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 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a selection of values and storing result in a variable
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a selection of values and storing result in a variable
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a selection of values and storing result in a variable
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Storing result of function directly in a variable instead of cell. | Excel Programming | |||
Storing a selection from a combo box in a variable | Excel Programming | |||
Storing a value to variable | Excel Discussion (Misc queries) | |||
Storing variable values in Excel worksheet | Excel Programming | |||
Storing selection & using the saved selection adress later | Excel Programming |