How to Paste Formulas into a Range using Excel VBA?

When a macro needs the same formula in a whole block of cells, you do not have to loop through them one at a time. Excel VBA can write a formula to an entire range in one line, copy one cell’s formula with PasteSpecial, or fill it down from the first row. This guide shows each method, when to use it, and the mistakes that produce wrong references.

Method 1: Write the Formula to the Whole Range

The simplest and fastest way is to assign a formula to the Formula property of the whole range. Write the formula as it should appear in the first cell. Excel adjusts the relative references for every other cell, exactly as if you had filled it down by hand.

Sub PasteFormulaIntoRange()
    ' A2 gets =B2*C2, A3 gets =B3*C3, and so on down to A10
    Range("A2:A10").Formula = "=B2*C2"
End Sub

Absolute references stay fixed, so "=B2*$F$1" multiplies every row by the value in F1.

Quotes inside the formula must be doubled, because the formula itself is a VBA string:

Range("D2:D10").Formula = "=IF(B2>100,""High"",""Low"")"

Size the range to the data

Hard-coding A2:A10 breaks as soon as the data grows. Find the last used row in a column that always has data, and build the range from it:

Sub PasteFormulaToLastRow()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    ws.Range("A2:A" & lastRow).Formula = "=B2*C2"
End Sub

Formula2 for dynamic array formulas

In Microsoft 365, use Formula2 when the formula uses dynamic array functions such as FILTER, UNIQUE or SORT, or when it should spill. Formula writes it the way older Excel versions would and can add an implicit intersection @ that stops it from spilling.

Range("E2").Formula2 = "=UNIQUE(B2:B100)"

Method 2: FormulaR1C1 (Same Formula in Every Cell)

FormulaR1C1 describes references by their position relative to the cell that holds the formula. RC[1] means “same row, one column to the right”; R[-1]C means “one row up, same column”. Because those offsets are the same for every cell in the range, the R1C1 text is identical everywhere, which makes it handy when you build formulas in code.

Sub PasteFormulaR1C1()
    ' In column A: multiply the cells one and two columns to the right
    Range("A2:A10").FormulaR1C1 = "=RC[1]*RC[2]"
End Sub

That produces the same result as Method 1. Only give FormulaR1C1 a formula written in R1C1 notation: a normal formula such as "=B2*C2" assigned to FormulaR1C1 is read as R1C1 and gives the wrong result or an error. Use R1C1 without brackets for an absolute reference, for example "=RC[1]*R1C6" for “times $F$1”.

Method 3: Copy One Cell and PasteSpecial the Formula

When the formula already exists in a cell, copy that cell and paste only its formula into the target range. References adjust the same way they do with Ctrl+C and Ctrl+V.

Sub CopyFormulaWithPasteSpecial()
    Range("A2").Copy
    Range("A3:A10").PasteSpecial Paste:=xlPasteFormulas
    Application.CutCopyMode = False   ' clear the copy marquee
End Sub

The Paste argument decides what comes across:

PasteSpecial also takes three optional behaviour arguments:

You can also paste array to range with VBA.

• SkipBlanks:=True leaves destination cells alone where the copied cell is blank.
• Transpose:=True turns copied rows into columns and columns into rows.
• Operation:=xlPasteSpecialOperationAdd (or Subtract, Multiply, Divide) combines the copied values with what is already in the destination.

Range("A2:A10").Copy
Range("C2").PasteSpecial Paste:=xlPasteFormulas, SkipBlanks:=True, Transpose:=True
Application.CutCopyMode = False

Method 4: FillDown From the First Row

FillDown copies the top cell of a range into every cell below it, like Ctrl+D. Call it on the whole range, not on the first cell alone:

Sub FillFormulaDown()
    Range("A2").Formula = "=B2*C2"
    Range("A2:A10").FillDown   ' copies A2 into A3:A10
End Sub

FillRight does the same across a row. Both also copy formatting from the top or left cell.

Which Method to Use

Writing to Formula or FormulaR1C1 does not use the clipboard, so it is faster and does not interfere with anything the user has copied.

Running the VBA Code

1. Press Alt+F11 to open the Visual Basic Editor.
2. Choose Insert > Module.
3. Paste the code into the new module.
4. Click inside the macro and press F5, or run it from Developer > Macros in Excel.

Save the workbook as .xlsm so the macro is kept.

Tips for Large Ranges

• Write once, not in a loop: one assignment to a 50,000-row range is far faster than 50,000 assignments to single cells.
• Pause screen updates and calculation while the macro runs, and turn them back on at the end:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("A2:A50001").Formula = "=B2*C2"

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

• Convert to values when the formulas are only needed once: rng.Value = rng.Value replaces the formulas with their results and makes the workbook lighter.
• Test on a few rows first, then widen the range.

Without VBA

For a one-off job, Excel’s own tools are quicker than writing a macro: double-click the fill handle to copy a formula down to the end of the data, select a range and press Ctrl+D to fill down, or select a range, type the formula and press Ctrl+Enter to enter it in every selected cell. Turning the data into a table (Ctrl+T) goes further: a formula typed in one row of a column fills the whole column automatically.

Final Thoughts

For most macros, Range("A2:A" & lastRow).Formula = "=B2*C2" is all you need: one line, no clipboard, and Excel adjusts the references. Reach for FormulaR1C1 when you generate formulas in code, PasteSpecial when the formula already lives in a cell, and FillDown when the formatting should come along too.

FAQs