Better way of applying a formula down a column in VBA?

gers1978 · Apr 29th 2016

I used the macro recorder to get the initial code and incorporated it into the following:

Code

Dim lastRow As Integer
    Dim lastRowInvoice As Integer
    
    lastRow = Worksheets("Sheet1").Range("A1").End(xlDown).Row
    lastRowInvoice = Worksheets("Invoice").Range("A1").End(xlDown).Row
    Columns("A:A").Select
    Selection.Copy
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Range("E1").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(VLOOKUP(RC[-4],Sheet1!R1C1:R" & lastRow & "C3,2,FALSE) & "" / "" & VLOOKUP(RC[-4],Sheet1!R1C1:R" & lastRow & "C3,3,FALSE))"
    Range("E1").Select
    Selection.AutoFill Destination:=Range("E1:E" & lastRowInvoice)
    Range("E1:E" & lastRowInvoice).Select
    Calculate
    Columns("E:E").EntireColumn.AutoFit

Display More

I'm thinking the line beginning "ActiveCell.FormulaR1C1" is not the best way to do this?
I'm trying to tidy up the code as best I can

Also posted here:

http://www.mrexcel.com/forum/e…ications.html#post4507123

**royUK** · Apr 30th 2016

Re: Better way of applying a formula down a column in VBA?

The best way is to use a Table which allows the use of Calculated Fields which means formulas are automatically added.

Calculated Fields

You can actually set formulas to copy down in Excel Options

File -> Options -> Advanced -> Editing options ->Extend data range formats and formulas

Better way of applying a formula down a column in VBA?

Participate now!

Share