VBA : Insert formula into new cells

  • I have a very large spreadsheet with multiple work books, i have protected certain cells in each worksheet, eg Column S calculates values from Q and R. So therefore i do not want users to be able to change my formula in S. I have created a macro to allow the users to insert rows into each active worksheet but each new row that is inserted does not contain any formula, i therefore need to come up with some code that takes formula from column S and inserts formula into new cell in column S! Only problem is the user can enter a number of new rows in each worksheet!

    Any help out there? i would be vrey grateful for any ideas

  • 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).

  • 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()
    Range("A65536").End(xlUp).Offset(-2, 2).Copy
    Range("A65536").End(xlUp).Offset(-1, 2).Select
    End Sub


    Thanks: ~Yogendra

  • 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()
        With Range("Q65536").End(xlUp)
            .Offset(-2, 2).Copy .Offset(-1, 2)
        End With
        Application.CutCopyMode = False
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!