Formula on every new row

AndrewPatterson 

You can automatically apply a formula to every new row that is added to your Excel sheet via a Microsoft Form. To achieve this, you can use Excel's built-in Table functionality, which automatically extends formulas and formats to new rows. Here’s how you can do it:

Steps to Automatically Apply Formulas to New Rows

1. Convert Your Data Range to a Table:
• Select the range of data that is linked to your Microsoft Form.
• Go to the Insert tab on the Ribbon.
• Click on Table. Ensure that the "My table has headers" option is checked if your data has headers.
• Click OK.
2. Add Your Formula to the Table:
• In the first row of the new column where you want the formula to appear, enter your formula.
• Press Enter.
3. Excel Table Features:
• When you add a formula to a cell in a table column, Excel automatically copies the formula to all other cells in that column.
• When new rows are added to the table (e.g., via your Microsoft Form), Excel will automatically apply the formula to the new rows.

Example:

Let’s say your Microsoft Form responses are populating columns A to D in your worksheet, and you want to add a formula in column E.

1. Create a Table:

Select the range A1

and all the data below it.

• Go to Insert > Table and create the table.
1. Add a Formula:
• In cell E2 (assuming row 1 contains headers), enter your desired formula. For example, if you want to sum columns B and C, you would enter =B2+C2 in E2.
• Press Enter.

Automatically Extending Formulas:

With the table feature, whenever a new row is added by your Microsoft Form, Excel will automatically apply the formula in column E to the new row.

Using VBA for More Complex Scenarios

If your scenario is more complex and requires a VBA solution (for example, if you need to apply multiple different formulas or perform more complex actions), you can use the Worksheet Change event. Here's a basic example:

1. Open VBA Editor:
• Press Alt + F11 to open the VBA editor.
2. Add Code to the Worksheet Module:
• Find your worksheet in the Project Explorer on the left.
• Double-click on the sheet where the form responses are added (e.g., Sheet1).
• Add the following code:

Vba Code is untested backup your file.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lastRow As Long
    Dim ws As Worksheet

    Set ws = Me

    ' Check if the change is in the range where form responses are added
    If Not Intersect(Target, ws.Columns("A:D")) Is Nothing Then
        Application.EnableEvents = False
        
        ' Find the last row with data
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        
        ' Apply formula to the new row (for example, in column E)
        ws.Cells(lastRow, "E").Formula = "=B" & lastRow & "+C" & lastRow
        
        Application.EnableEvents = True
    End If
End Sub

Explanation:

• Worksheet_Change Event: This event triggers every time a change is made to the worksheet.
• Intersect: This checks if the change was made in the columns where the form responses are added.
• lastRow: This finds the last row with data in column A.
• Formula: This applies the formula to the new row in column E.

Note:

• This VBA code is a basic example. Depending on your specific needs, you may need to adjust it.
• Make sure to save your workbook with macros enabled (.xlsm format).

By using either the Table feature or VBA, you can ensure that formulas are automatically applied to new rows added by your Microsoft Form. The text, steps and vba code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.