Insert Active Formula in Excel Cell with VBA

**trex46** · 07-03-2008, 12:19 PM

I am trying to get my VBA code to insert an active "Total" formula (ie like an autosum entry into an excel cell of "=Sum(D3:D56)") to sum a column of values, and update any subsequent changes to the values in that column. I can get the VBA code to sum the column and enter the result in the cell but it then does not change when a value in that cell changes.

I am a new VBA user so any help would be appreciated.

**dominicb** · 07-03-2008, 12:28 PM

Good afternoon trex46

...and welcome to the forum!!

Does something like this work out OK for you :

Range("A15").Formula = "=Sum(A1:A10)"

HTH

DominicB

**trex46** · 07-04-2008, 10:35 AM

Thanks very much dominicb for your help.

It was not exactly what I wanted and no doubt due to my poor explanation of my needs. The issue was that the cell receiving the sum formula (A15 in your formula) and the end cell of data (A10 in your formula) can vary depending on the amount of data lines in the spreadsheet.

However what you suggested gave me enough to then come up with the following;-

Sub SumMacro()

Dim DataEndAdr As String
Dim TotalRowAdr As String

    Range("A4").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 11).Select
    DataEndAdr = ActiveCell.Address
    ActiveCell.Offset(2, 0).Select
    TotalRowAdr = ActiveCell.Address
    Range(TotalRowAdr).Formula = "=Sum($L$4:" & DataEndAdr & ")"
   
End Sub

It provided a result although there may be a better and shorter way of doing it. If so I would be happy to receive the suggestion.

Thanks again
trex46

**shg** · 07-04-2008, 11:44 AM

Maybe

Sub SumMacro()
    Range("A4").End(xlDown).Offset(2, 11).FormulaR1C1 = "=sum(r4c12:r[-2]c12)"
End Sub

Insert Active Formula in Excel Cell with VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Insert Active Formula in Excel Cell with VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions