VBA code for summing an already selected range of cells

[GDH]

Hi all,

I often find myself in the following situation - I will highlight a range of numbers to see what the sum is in the bottom right hand corner (the cells which I select areoften not all within the same column or are not consecutively listed under each other) .Depending on the circumstances I will then type out a SUM formula with the range to put that value somewhere in the spreadsheet - the problem being that I have to deselect the cells and then re-select these within the formula. When I have selected many numbers, it is not always easy to remember every number which I included.

As a way to remember the numbers, I format those cells in a certain colour before I do the SUM formula and would then include only those coloured cells in the formula. I would like to avoid this extra bit of work.

I am new to VBA but would like some form of code that allows me sum the contents of a selected range and paste that formula beneath the last value in the range - the range would have been selected before running the maco.

Can anyone provide some suggestions?
Thanks in advance

 

[RoryA]

Try this:

Sub addsumformula()
   Dim rng As Range
   With Selection.Areas
      With .Item(.Count)
         .Offset(.Rows.Count).Resize(1).Formula = "=SUM(" & Selection.Address & ")"
      End With
   End With
End Sub

 

[GDH]

Hi Rory

That works perfectly! Thank you.

However, would it be possible for the macro to let me select which cell to place my SUM formula? I noticed that sometimes, the last cell of my selected range is not always blank but is part of the data set.

GDH

 

[RoryA]

Sure:

Sub addsumformula()
   Dim strFormula As String
   Dim rngOut As Range
   strFormula = "=SUM(" & Selection.Address & ")"
   On Error Resume Next
   Set rngOut = Application.InputBox(prompt:="Select cell to put sum formula in", Type:=8)
   If Not rngOut Is Nothing Then rngOut.Formula = strFormula
End Sub

 

[GDH]

Thank you so much - works perfectly!

 

[RoryA]

Glad to help.

 

[adygelber]

Hello,

I found this thread searching for a way to sum random cells and it helps me a lot, but I need something more. I noticed that the sums are made usind absolute references

=SUM($AB$7;$AF$7;$AJ$7;$AN$7;$AR$7;$AV$7;$AZ$7;$BD$7;$BH$7;$BL$7;$BP$7)

and i need to multiply this formula.
There is any way to remove the absolute references $?

Thanks!

 

[adygelber]

Solved:

"=SUM(" & Selection.Address.Address(False, False) & ")"

Hello,

I found this thread searching for a way to sum random cells and it helps me a lot, but I need something more. I noticed that the sums are made usind absolute references

=SUM($AB$7;$AF$7;$AJ$7;$AN$7;$AR$7;$AV$7;$AZ$7;$BD$7;$BH$7;$BL$7;$BP$7)

and i need to multiply this formula.
There is any way to remove the absolute references $?

Thanks!