Excel VBA: AutoFill Multiple Cells with Formulas

Solution 1:

The approach you're looking for is FillDown. Another way so you don't have to kick your head off every time is to store formulas in an array of strings. Combining them gives you a powerful method of inputting formulas by the multitude. Code follows:

Sub FillDown()

    Dim strFormulas(1 To 3) As Variant

    With ThisWorkbook.Sheets("Sheet1")
        strFormulas(1) = "=SUM(A2:B2)"
        strFormulas(2) = "=PRODUCT(A2:B2)"
        strFormulas(3) = "=A2/B2"

        .Range("C2:E2").Formula = strFormulas
        .Range("C2:E11").FillDown
    End With

End Sub

Screenshots:

Result as of line: .Range("C2:E2").Formula = strFormulas:

Result as of line: .Range("C2:E11").FillDown:

Of course, you can make it dynamic by storing the last row into a variable and turning it to something like .Range("C2:E" & LRow).FillDown, much like what you did.

Hope this helps!

Solution 2:

Based on my Comment here is one way to get what you want done:

Start byt selecting any cell in your range and Press Ctrl + T

This will give you this pop up:

make sure the Where is your table text is correct and click ok you will now have:

Now If you add a column header in D it will automatically be added to the table all the way to the last row:

Now If you enter a formula into this column:

After you enter it, the formula will be auto filled all the way to last row:

Now if you add a new row at the next row under your table:

Once entered it will be resized to the width of your table and all columns with formulas will be added also:

Hope this solves your problem!