AutoFill formula once new data is entered

Question

I have Data starting from E3:E10. I have a Formula in I4. The formula in I4 = E4-E3.

What is the VBA code to autofill the formula down once I add more data (E11,E12,etc) automatically.

I have tried various codes and they all work to autofill once the sub is ran. But after I add more data, I have to run the sub again to Autofill.

(latest code I tried)

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("projectmomo")
Set StartCell = Range("E3")

'Refresh UsedRange
  Worksheets("projectmomo").UsedRange

'Find Last Row
  LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'Select Range
  sht.Range("E3:I" & LastRow).Select
  
  sht.Range("I4:I" & LastRow).Value = "=minus(RC[-4],RC[-3])"

(other code I tried)

LastRow = Range("E" & Rows.Count).End(xlUp).Row

Range("I4:I" & LastRow).Function = "RC[-4]-R[-1]C[-4]"

Again, both work and adjust fields once Sub is ran. However, when data is added to row E, formula does not autfill down to the cell where the new data is entered.

Answer 1

Your formula is using R1C1 type, where you didn’t indicate the formula as R1C1 type.

Try this

range(“i1”).formular1c1 = [your formula]

By the way

There a a command can do better about your job

.range(“I1” ).resize (last row-1).filldown