Inserting new row doesnt updat autosum

[ipbr21054]

Morning,

I use the code below to transfer my userform values to my selected row on the worksheet.
Currently the autom in cell K25 is =SUM(K9:K24)

The last row with data in it at present is row 24

When i next use the form an insert to worksheet i see the new row with values added but was expecting the autosum code to update itself of which it doesnt.
Reason being now i have a new the last row with values in it is row 25 but the autosum doesnt update to then cover the new row

Private Sub CommandButton1_Click()
 Dim i As Integer
 Dim ControlsArr As Variant, ctrl As Variant
 Dim x As Long
 Dim z As Integer
 z = CInt(Application.InputBox("INSERT DATA TO WHICH ROW ?", "ROW NUMBER MESSAGE BOX", Type:=1))
 For i = 1 To 11
 With Me.Controls("TextBox" & i)
 If .Text = "" Then
 MsgBox "ALL FIELDS MUST BE COMPLETED", 48, "GRASS NEW CUSTOMER FORM"
 .SetFocus
 Exit Sub
 End If
 End With
 Next i
 
 ControlsArr = Array(Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.TextBox4, Me.TextBox5, Me.TextBox6, Me.TextBox7, Me.TextBox8, Me.TextBox9, Me.TextBox10, Me.TextBox11)
  With ThisWorkbook.Worksheets("GRASS")
 .Rows(z).EntireRow.Insert Shift:=xlDown
 For i = 0 To UBound(ControlsArr)
 Select Case i
 Case 3
 .Cells(z, i + 1) = Val(ControlsArr(i))
 ControlsArr(i).Text = ""
 Case Else
 .Cells(z, i + 1) = ControlsArr(i)
 ControlsArr(i).Text = ""
 End Select
 Next i
 End With
 
 Application.ScreenUpdating = False
 ActiveWorkbook.Save
 Application.ScreenUpdating = True
 With ThisWorkbook.Worksheets("GRASS")
 .Range("A5").Select
 .Range("A4").Select
 MsgBox "DATABASE HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
 End With
 Unload GRASSNEWCUSTOMER
End Sub

 

[footoo]

One way is to change the formula in K25 to : =SUM(K9:OFFSET(K25,-1,0))

 

[ipbr21054]

Perfect thanks