CODEWITHSUNDEEP
excelvba
d3mo
d3mo

Reputation: 9

VBA Insert Column Header & Fill Formula on Column

'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.

Upvotes: 0

Views: 1679

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

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

Upvotes: 0

Related Questions