How to Auto Fill 2 columns VB Code

[kevin440red]

I recorded a Macro and now I'm trying to edit it to auto fill to the last row.
this is what i have so far.
Thanks

Range("C2").Select    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=-234
    Application.Run "'ILCHotSheet (v3).xlsx'!BAZINGA_007"
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("C1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("N2").Select
    [COLOR=#ff0000]ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-11],Sheet2!RC[-13]:R[13666]C[-12],2,0)[/COLOR]"
[COLOR=#ff0000]    Range("O2").Select[/COLOR]
[COLOR=#ff0000]    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-12],Sheet2!RC[-14]:R[13666]C[-12],3,0)"[/COLOR]
[COLOR=#ff0000]    Range("N2:O2").Select[/COLOR]
[COLOR=#ff0000]    Selection.AutoFill Destination:=Range("N2:O2")[/COLOR]
[COLOR=#ff0000]    Range("N2:O2").Select[/COLOR]
[COLOR=#ff0000]    Range("N2:O2").AutoFill Destination:=Range("N2:N, O2:0" & lastRow)[/COLOR]
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

 

[Joe4]

Welcome to the Board!

You do no actually need to autofill, you can apply the whole formula at once. Just identify a column we can use to locate the last row you want to copy down to. Since autofill usually looks left, I am going to assume that column M is such a column.

So, we can apply our formulas like this:

Dim lastRow as Long
lastRow = Cells(Rows.Count,"M").End(xlUp).Row
Range("N2:N" & lastRow).FormulaR1C1 = "=VLOOKUP(RC[-11],Sheet2!RC[-13]:R[13666]C[-12],2,0)"
Range("O2:O" & lastRow).FormulaR1C1 = "=VLOOKUP(RC[-12],Sheet2!RC[-14]:R[13666]C[-12],3,0)"

 

[kevin440red]

Excellent

Thank you for your help

 

[Joe4]

You are welcome.
Glad I was able to help!