VBA Script to Sum from Active Cell to Top of Column

How would I substitute a full column rather than a static range in a summation formula for a cell?

similar to this which selects up to the top of the active column

       Range(Selection, Selection.End(xlUp)).Select

but use it in the formula below so as not to choose a static range for the sum?

       ActiveCell.FormulaR1C1 = "=(SUM(R[-8]C:R[-1]C)/100)"

kimbe1jmAsked:
Who is Participating?
 
JoaTexConnect With a Mentor Commented:
Hi

Adapt the Code to your Formula, Here each Cell is divided by 100.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Static bCritical As Boolean

   On Error GoTo err_Handler
   If Not bCritical Then
       bCritical = True
   Else
       Exit Sub
   End If
   
   For Each Cell In Target.Cells
       If Cell.Column = 1 And Cell.Row >= 1  Then
           Cell.Value = Cell.Value / 100
       End If
               
   Next Cell
   
err_Handler:
   bCritical = False
End Sub

Hope it Suites you
Jo
0
All Courses

From novice to tech pro — start learning today.