Paste a formula into a range of cells using macros

Question

I'm trying to paste a formula into a range of cells (I only want it to paste until the last used row of column K), but I keep getting a syntax error

With ThisWorkbook
With .Sheets("Ex")
    lRow4 = .Cells(.Rows.count, 1).End(xlUp).Row
    Set rng4 = .Range("J6:J" & lRow4)
    rng4. FormulaR1C1."=IF(RC[1]='Lease & RPM Charges'!R[-4]C[-6],UPPER(TEXT(REPLACE(REPLACE('Lease & RPM Charges'!R[-4]C[-7],5,0,""-""),8,0,""-""),""DD-mmm-YY"")))"
End With

End With
End Sub

Pᴇʜ Pᴇʜ · Accepted Answer · 2018-10-15T14:22:51

Note that

.Cells(.Rows.count, 1).End(xlUp).Row

gives you the last used row of column 1 which is column A.
You can use …

.Cells(.Rows.count, "K").End(xlUp).Row

instead, to get the last used row in column K.

Also rng4. FormulaR1C1. should be rng4.FormulaR1C1 =

With ThisWorkbook.Sheets("Ex")
    lRow4 = .Cells(.Rows.count, "K").End(xlUp).Row
    Set rng4 = .Range("J6:J" & lRow4)
    rng4.FormulaR1C1 = "=IF(RC[1]='Lease & RPM Charges'!R[-4]C[-6],UPPER(TEXT(REPLACE(REPLACE('Lease & RPM Charges'!R[-4]C[-7],5,0,""-""),8,0,""-""),""DD-mmm-YY"")))"
End With

Paste a formula into a range of cells using macros

1 Answers