VBA : Insert formula into new cells

Welcome to the OzGrid Forum!

I don't believe you can catch just the insert action easily. However the following Change event will ensure that column S always has a formula, once you modify it to reflect the correct formula. If your sheet is protected, with a password, you will also need to suppy it at the appropriate spots. Add the code to the sheet module (right click on the sheet tab and select View Code).

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim c As Range
    For Each c In Target
        If IsEmpty(Cells(c.Row, 19)) Then
            Application.EnableEvents = False
            Me.Unprotect 'add password if needed
            Cells(c.Row, 19).FormulaR1C1 = "Insert desired formula here"
            Me.Protect 'add password if needed
            Application.EnableEvents = True
        End If
    Next c
End Sub

Oops -- Derk beat me (again, as usual) with a better solution. I forgot about handling protected cells. Go, Professor. :yes:

You could use a macro that checks the length of cells in column S and one of yoru data columns (let's say R), and then fills column S with your formula if S is empty and the data column does have an entry.

Sub Test()
Dim i As Integer
For i = 1 To 25
    If Len(Range("S" & i)) = 0 Then
        If Len(Range("R" & i)) > 0 Then
        Range("S" & i).Formula = "=INT(RAND()*100)"
        End If
    End If
Next i
End Sub

Make sure that "i" loops beyond your range of possible rows.

If your formula needs data form only one of the data columns to work, use an "OR" condition and check the LEN of both columns Q and R for that IF statement.

I have attached a sheet in which i tried to create the scenario of copying the formula above the cell instead of having fixed formula.

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect
Range("A65536").End(xlUp).EntireRow.Insert
Range("A65536").End(xlUp).Offset(-2, 2).Copy
Range("A65536").End(xlUp).Offset(-1, 2).Select
ActiveSheet.Paste
ActiveSheet.Protect
End Sub

HTH

yjoshi raises a good point. How are the rows being inserted with the sheet protected? If it's being done with macro, then that is how the formula should be replicated. Here is a version of yjoshi's macro that eleiminates the select and inserts the row just before the last entry in column Q.

Private Sub CommandButton1_Click()
    ActiveSheet.Unprotect
    With Range("Q65536").End(xlUp)
        .EntireRow.Insert
        .Offset(-2, 2).Copy .Offset(-1, 2)
    End With
    Application.CutCopyMode = False
    ActiveSheet.Protect
End Sub