VBA Insert Column Header & Fill Formula on Column

Question

'Find the last used row in a Column: column A in this example

Dim lastRow As Long


With Worksheets("Summary")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'Inserts column on E
Columns("E:E").Select
Selection.Insert Shift:=xlToRight

'Titles header "Net Return"
Range("E3").FormulaR1C1 = "Net Return"
'Places formula in cell E4
Range("E4").FormulaR1C1 = "=RC[-2]-RC[-3]"
'Fills formula down row - this is where my code breaks
Range("E4").AutoFill Destination:=Range("E4:(lastRow - 1)"), Type:=xlFillDefault

I want to insert new column on E, input the formula =(C4-B4) in cell E4 and fill down until lastRow. How can I utilize lastRow when declaring the range of cells to populate my formula in? I receive runtime error 1004 on my .Autofill line.

What is the "last row" in a brand-new empty column? How is that to be determined? — Tim Williams
Good point, I suppose my method won't work then unless it determines last row based upon column A, which will always have the maximum number of rows. I can perform a last row check on column A and subtract one from it and store that in lastRow? I am unsure how that code looks — d3mo
Why not just Range(Cells(4,5), Cells(lastRow-1, 5)).FillDown? Or, even, skip that step and just do it in one: Range("E4:E" & (lastRow-1)).FormulaR1C1 = "=RC[-2]-RC[-3]" — Chronocidal

Tim Williams Tim Williams · Accepted Answer · 2019-10-18T18:43:20

You could do something like this:

Sub SetUpSummarySheet()

    Dim ws As Worksheet

    Set ws = Worksheets("Summary")

    InsertWithHeaderAndFormula ws, "E", "Net Return", "=RC[-2]-RC[-3]"
    InsertWithHeaderAndFormula ws, "G", "Blah", "=RC[-1]"
    'etc for other solumns

End Sub

'Insert a column in sheet ws, add a header hdr and a formula sForm to extend
'   as far down as there are values in ColA
Sub InsertWithHeaderAndFormula(ws As Worksheet, colLetter As String, _
                                hdr As String, sForm As String)

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ws.Cells(1, colLetter).EntireColumn.Insert Shift:=xlToRight
    ws.Cells(3, colLetter).Value = hdr
    ws.Cells(4, colLetter).Resize(lastRow - 3, 1).FormulaR1C1 = sForm

End Sub

VBA Insert Column Header & Fill Formula on Column

1 Answers