VBA Code- Insert formula up to last row

[Johnatha]

Hi,

I'm looking to insert a formula onto a sheet using a macro. So far my code works and looks like this


Range("D:D").FormulaR1C1 = "=IFERROR(RC[-1]/RC[-2],"""")"

The problem is that it inserts the formula for the entire column D. I'd like to have the formula start from cell "D1" and finish wherever my last cell in column C is located (for example, "C75" so range "D1 : D75").

Is there any way I can do this? I sort of know how to use LastRow but not sure how to apply it here. Still a newbie :cool:

Thanks!
Johnathan

[JKwan]

give this a try


Sub InsertFormula()
Dim LastRow As Long

LastRow = FindLastRow(ActiveSheet, "C") ' ActiveSheet may or may not work for you. you can specify the sheet name like WorkSheets("name")
Range("D1:D" & LastRow).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-2],"""")"
End Sub
Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
FindLastRow = WS.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function

[Bob Phillips]

Or


Range("D1").Resize(Range("C1").End(xlDown).Row).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-2],"""")"