copy sum selection

[Guest]

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

 

[Bob Phillips]

Andrew,

Why not just turn on the macro recorder, and record it?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[David McRitchie]

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

 

[Mike Fogleman]

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.Selection)
End Sub

Mike F

 

[Tom Ogilvy]

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.

 

[Guest]

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]

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

 

[Guest]

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]

My typo.

Regards,
Tom Ogilvy

Tom,

Had to change declaration of rng1 to Range but other than that works like a
charm.

Thanks for all your help.

Andrew.