Autofill working but giving an error when trying to proceed with the rest of code-CodePudding

I am working with making Vlookups for entire columns, and have the autofill working, yet after I get either "449: argument not optional" "1004, autofill method of range class failed"

I have tried just selection.autofill, and adding the parameters for

Destination:=Range("AJ2", Range("AJ2").End(xlDown))

Type:=xlFillDefault

I also have failed to avoid select statements, these couple lines are plaguing me. Any help appreciated.


Dim rowcount As Integer
Dim sht5 As Worksheet, sht6 As Worksheet, sht7 As Worksheet
Dim r As Range
Set sht5 = Sheets("oldoutput")
Set sht6 = Sheets("newoutput")
Set sht7 = Sheets("rolls")
rowcount = Application.CountA(Range("A:A"))

sht6.Activate
    Range("AJ2").Select
        Selection.FormulaR1C1 = "=VLOOKUP(RC[-35],oldoutput, 1, FALSE)"
    Range(Range("AJ2"), Range("AJ2").End(xlDown)).Select
        Selection.AutoFill Destination:=Range("AJ2", Range("AJ2").End(xlDown)), 
        Type:=xlFillDefault

CodePudding user response：

For the AutoFill method, the source is where the formula or pattern currently is, and then the destination is where to extend it to.

Since you put the formula into "AJ2", you should use "AJ2" as the source for the Autofill, not the whole "AJ2:AJ_" range. Eg. Range("AJ2").AutoFill Destination:=...

Sub Example()
    Dim rowcount As Integer
    Dim sht5 As Worksheet, sht6 As Worksheet, sht7 As Worksheet
    Dim r As Range
    Set sht5 = Sheets("oldoutput")
    Set sht6 = Sheets("newoutput")
    Set sht7 = Sheets("rolls")
    rowcount = Application.CountA(Range("A:A"))

    sht6.Range("AJ2").FormulaR1C1 = "=VLOOKUP(RC[-35],oldoutput, 1, FALSE)"
    sht6.Range("AJ2").AutoFill Destination:=sht6.Range("AJ2", sht6.Range("AJ2").End(xlDown)), Type:=xlFillDefault
    
End Sub

An alternate cleaner version of those last two lines:

With sht6.Range("AJ2")
    .FormulaR1C1 = "=VLOOKUP(RC[-35],oldoutput, 1, FALSE)"
    .AutoFill Destination:=sht6.Range("AJ2", .End(xlDown)), Type:=xlFillDefault
End With

A second alternate version that even avoids repeating the address:

With sht6.Range("AJ2")
    .FormulaR1C1 = "=VLOOKUP(RC[-35],oldoutput, 1, FALSE)"
    .AutoFill Destination:=.Parent.Range(.Cells(1), .End(xlDown)), Type:=xlFillDefault
End With