Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy sum selection
Im trying to make a macro that creates a sum formula of the cells that have
been selected (like the sum in the status bar) so that I can then click another cell and paste the resulting formula. I dont know much vba so was wondering if anyone had some example code that could do this. Cheers, Andrew |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy sum selection
Andrew,
Why not just turn on the macro recorder, and record it? -- HTH RP (remove nothere from the email address if mailing direct) "Andrew" wrote in message ... Im trying to make a macro that creates a sum formula of the cells that have been selected (like the sum in the status bar) so that I can then click another cell and paste the resulting formula. I dont know much vba so was wondering if anyone had some example code that could do this. Cheers, Andrew |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy sum selection
Hi Andrew,
For an indication of what you actually see on the statusbar see http://www.mvps.org/dmcritchie/excel/statusbar.htm The SUM toolbar icon will not sum a SUM, and has several idiosyncrasies to compensate for various implementation. The area to be summed must be contiguous. If you just want a total below a column of numbers, and want to insert rows later suggest something like the following in you workbook and no macro would be needed.. Example below, for more information see http://www.mvps.org/dmcritchie/excel/offset.htm which also includes an Event macro to simulate use of the SUM toolbar icon.bit with OFFSET as shown below.so you won't have to update the formula manually.. . b2: 3.00 b3: 5.00 b4: 6.70 b5: =SUM(B2:OFFSET(B5,-1,0)) so if you insert a row after b4 and place a number in it you would then have b5: 3.33 b6: =SUM(B2:OFFSET(B6,-1,0)) You can use the fill handle to replicate the formula across for additional columns. http://www.mvps.org/dmcritchie/excel/fillhand.htm For a macro similar to what you ask for you would use code similar to the event macro in offset.htm and then force the user to make another selection while the macro is running. 1) make a selection 2) run a macro that might use sumx = "=SUM(" & selection.address & ")" but would be better to use OFFSET in the formula. 3) ask for you to select the cell for the sum 4) plop formula into cell selection.formula = sumx Macro not supplied, because I think there are better alternatives already mentioned. i.e. offset.htm page If the selection is a bunch of discontiguous cells and you want a formula and not a value, you should be aware that there is a specification limit on the number of characters in a formula. Length of formula contents: 1,024 characters --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Andrew" wrote ... Im trying to make a macro that creates a sum formula of the cells that have been selected (like the sum in the status bar) so that I can then click another cell and paste the resulting formula. I dont know much vba so was wondering if anyone had some example code that could do this. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy sum selection
This does not create a formula in a cell, but it does put the sum of the
selected cells in E2. Make your cell selections, then run this macro. Then the resulting value in E2 can be pasted wherever you want. Sub Macro1() Range("E2").Value = Application.WorksheetFunction.Sum(Application.Sele ction) End Sub Mike F "Andrew" wrote in message ... Im trying to make a macro that creates a sum formula of the cells that have been selected (like the sum in the status bar) so that I can then click another cell and paste the resulting formula. I dont know much vba so was wondering if anyone had some example code that could do this. Cheers, Andrew |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy sum selection
Sub MakeFormula()
Dim rng As Range, rng1 As Range Set rng = Selection On Error Resume Next Set rng1 = Application.InputBox("Select cell to hold formula", Type:=8) On Error GoTo 0 If rng1 Is Nothing Then Exit Sub rng1.Formula = "=Sum(" & rng.Address(0, 0) & ")" End Sub Select your cells, then run the macro. -- Regards, Tom Ogilvy "Andrew" wrote in message ... Im trying to make a macro that creates a sum formula of the cells that have been selected (like the sum in the status bar) so that I can then click another cell and paste the resulting formula. I dont know much vba so was wondering if anyone had some example code that could do this. Cheers, Andrew |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy sum selection
Tom,
Thanks for your code, it works great. Do you know if its possible to bypass the input box that appears and just have the formula created put into the clipboard so that it can be pasted in any cell with a ctrl+v? With my limited vb knowledge, Im thinking one of the objects would need to be a string and I was playing around with the following code but didnt have much luck. Do you have any thoughts? Sub sumsel2() Dim rng As Range, rng1 As String Set rng = Selection rng1 = "=Sum(" & rng.Address(0, 0) & ")" rng1.Select Selection.Copy End Sub Thanks again. Andrew "Tom Ogilvy" wrote: Sub MakeFormula() Dim rng As Range, rng1 As Range Set rng = Selection On Error Resume Next Set rng1 = Application.InputBox("Select cell to hold formula", Type:=8) On Error GoTo 0 If rng1 Is Nothing Then Exit Sub rng1.Formula = "=Sum(" & rng.Address(0, 0) & ")" End Sub Select your cells, then run the macro. -- Regards, Tom Ogilvy "Andrew" wrote in message ... Im trying to make a macro that creates a sum formula of the cells that have been selected (like the sum in the status bar) so that I can then click another cell and paste the resulting formula. I dont know much vba so was wondering if anyone had some example code that could do this. Cheers, Andrew |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy sum selection
Sub sumsel2()
Dim rng As Range, rng1 As rng set rng1 = Range("IV1") Set rng = Selection rng1.Formula = "=Sum(" & rng.Address & ")" rng1.Copy End Sub -- Regards, Tom Ogilvy "Andrew" wrote in message ... Tom, Thanks for your code, it works great. Do you know if its possible to bypass the input box that appears and just have the formula created put into the clipboard so that it can be pasted in any cell with a ctrl+v? With my limited vb knowledge, Im thinking one of the objects would need to be a string and I was playing around with the following code but didnt have much luck. Do you have any thoughts? Sub sumsel2() Dim rng As Range, rng1 As String Set rng = Selection rng1 = "=Sum(" & rng.Address(0, 0) & ")" rng1.Select Selection.Copy End Sub Thanks again. Andrew "Tom Ogilvy" wrote: Sub MakeFormula() Dim rng As Range, rng1 As Range Set rng = Selection On Error Resume Next Set rng1 = Application.InputBox("Select cell to hold formula", Type:=8) On Error GoTo 0 If rng1 Is Nothing Then Exit Sub rng1.Formula = "=Sum(" & rng.Address(0, 0) & ")" End Sub Select your cells, then run the macro. -- Regards, Tom Ogilvy "Andrew" wrote in message ... Im trying to make a macro that creates a sum formula of the cells that have been selected (like the sum in the status bar) so that I can then click another cell and paste the resulting formula. I dont know much vba so was wondering if anyone had some example code that could do this. Cheers, Andrew |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy sum selection
Tom,
Had to change declaration of rng1 to Range but other than that works like a charm. Thanks for all your help. Andrew. "Tom Ogilvy" wrote: Sub sumsel2() Dim rng As Range, rng1 As rng set rng1 = Range("IV1") Set rng = Selection rng1.Formula = "=Sum(" & rng.Address & ")" rng1.Copy End Sub -- Regards, Tom Ogilvy "Andrew" wrote in message ... Tom, Thanks for your code, it works great. Do you know if its possible to bypass the input box that appears and just have the formula created put into the clipboard so that it can be pasted in any cell with a ctrl+v? With my limited vb knowledge, Im thinking one of the objects would need to be a string and I was playing around with the following code but didnt have much luck. Do you have any thoughts? Sub sumsel2() Dim rng As Range, rng1 As String Set rng = Selection rng1 = "=Sum(" & rng.Address(0, 0) & ")" rng1.Select Selection.Copy End Sub Thanks again. Andrew "Tom Ogilvy" wrote: Sub MakeFormula() Dim rng As Range, rng1 As Range Set rng = Selection On Error Resume Next Set rng1 = Application.InputBox("Select cell to hold formula", Type:=8) On Error GoTo 0 If rng1 Is Nothing Then Exit Sub rng1.Formula = "=Sum(" & rng.Address(0, 0) & ")" End Sub Select your cells, then run the macro. -- Regards, Tom Ogilvy "Andrew" wrote in message ... Im trying to make a macro that creates a sum formula of the cells that have been selected (like the sum in the status bar) so that I can then click another cell and paste the resulting formula. I dont know much vba so was wondering if anyone had some example code that could do this. Cheers, Andrew |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy sum selection
My typo.
Regards, Tom Ogilvy "Andrew" wrote in message ... Tom, Had to change declaration of rng1 to Range but other than that works like a charm. Thanks for all your help. Andrew. "Tom Ogilvy" wrote: Sub sumsel2() Dim rng As Range, rng1 As rng set rng1 = Range("IV1") Set rng = Selection rng1.Formula = "=Sum(" & rng.Address & ")" rng1.Copy End Sub -- Regards, Tom Ogilvy "Andrew" wrote in message ... Tom, Thanks for your code, it works great. Do you know if its possible to bypass the input box that appears and just have the formula created put into the clipboard so that it can be pasted in any cell with a ctrl+v? With my limited vb knowledge, Im thinking one of the objects would need to be a string and I was playing around with the following code but didnt have much luck. Do you have any thoughts? Sub sumsel2() Dim rng As Range, rng1 As String Set rng = Selection rng1 = "=Sum(" & rng.Address(0, 0) & ")" rng1.Select Selection.Copy End Sub Thanks again. Andrew "Tom Ogilvy" wrote: Sub MakeFormula() Dim rng As Range, rng1 As Range Set rng = Selection On Error Resume Next Set rng1 = Application.InputBox("Select cell to hold formula", Type:=8) On Error GoTo 0 If rng1 Is Nothing Then Exit Sub rng1.Formula = "=Sum(" & rng.Address(0, 0) & ")" End Sub Select your cells, then run the macro. -- Regards, Tom Ogilvy "Andrew" wrote in message ... Im trying to make a macro that creates a sum formula of the cells that have been selected (like the sum in the status bar) so that I can then click another cell and paste the resulting formula. I dont know much vba so was wondering if anyone had some example code that could do this. Cheers, Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
How to 'copy' a drop down box selection? | Excel Discussion (Misc queries) | |||
Copy Selection | Excel Discussion (Misc queries) | |||
A Macro for copy with selection.. (please help me) | Excel Programming | |||
row selection and cut + copy | Excel Programming |