Improve/Optimize code

[pingpongplaya10]

Hello all,

I wrote the sub routine below (it works) but am interested in making it more streamlined and dynamic with the use of a function and passing a range of cells to calculate a sum. Can someone please provide some suggestions on how to make this happen?

The way it works is that it will automatically enter a formula which sums the numbers above the selected cell. For example, if there are numbers in cells A1 through A5, clicking on A6 would call the sub and enter a formula into A6 to sum the five cells above it.

Thanks in advance.

By the way, I'm new to Excel VBA, so if anybody has any suggestions on "cleaning" up my code, please let me know.

Private Sub SumCells()  Dim EndCell As String Dim StartCell As String, StartCell2 As String  StartCell = ActiveCell.Address  StartCell2 = ActiveCell.Offset(-1, 0).Address  Range(ActiveCell.Address, Selection.End(xlUp)).Select EndCell = Selection.End(xlUp).Address      Range(StartCell).Value = "=sum(" & EndCell & ":" & StartCell2 & ")" Range(StartCell).Select  End Sub


</pre>

 

[pingpongplaya10]

sorry, code didn't paste correctly... reposting...

Private Sub SumCells()

Dim EndCell As String
Dim StartCell As String, StartCell2 As String

StartCell = ActiveCell.Address

StartCell2 = ActiveCell.Offset(-1, 0).Address

Range(ActiveCell.Address, Selection.End(xlUp)).Select
EndCell = Selection.End(xlUp).Address
    
Range(StartCell).Value = "=sum(" & EndCell & ":" & StartCell2 & ")"
Range(StartCell).Select

End Sub

 

[Norie]

Can't you just use the AutoSum button on the toolbar?

 

[pingpongplaya10]

You are correct, AutoSum performs the same exact operation. However, to try and improve at writing reusable code, I was interested in seeing other ways of re-writing this.

 

[CyFoo]

select the first cell instead

ActiveCell.End(xlDown).Offset(1, 0) = "=sum(" & ActiveCell.Address & ":" & ActiveCell.End(xlDown).Address & ")"
ActiveCell.End(xlDown).Activate

 

[Norie]

What range do you want to sum?

If you wanted to sum from the first row to the selected row:

ActiveCell.Offset(1).FormulaR1C1 = "=SUM(R1C:R[-1]C)"

That puts the formula in the cell below the active cell.

 

[Weaver]

maybe:

Sub meh()
    With ActiveCell
        .Formula = "=sum(" & Range(.Offset(-1), .Offset(-1).End(xlUp)).Address & ")"
    End With
End Sub