CODEWITHSUNDEEP
vbaexcelautofill
Philip
Philip

Reputation: 189

Fill down in VBA

I want to insert a new column, paste a formula, and fill that formula down to the end of the data that is in Column A. I have gotten everything to work except for the fill down procedure. It just fails when it gets to the offset. Is there a way to do this in VBA? Any help would be greatly appreciated! 

Sub Macro3()

Sheet7.Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Sheet7.Cells(1, 2).Value = "Reason Code"
Sheet7.Cells(2, 2).Value = "=INDEX(ImportsFromMasterData!$B:B,MATCH(Imports!$A:A,ImportsFromMasterData!$A:A,0))"

Sheet7.Range("B2").Select
Selection.AutoFill Destination:=Range(Range("B2"), Range(Range("A2").End(xlDown)).Offset(, 2)), Type:=xlFillDefault

End Sub

Upvotes: 1

Views: 1615

Answers (2)

Tim Williams
Tim Williams

Reputation: 166306

Sub Macro3()

    With Sheet7

        .Columns("B:B").Insert Shift:=xlToRight, _
                        CopyOrigin:=xlFormatFromLeftOrAbove

        .Cells(1, 2).Value = "Reason Code"

        .Range(.Cells(2, 2), .Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)).Formula = _
           "=INDEX(ImportsFromMasterData!$B:B," & _
           "MATCH(Imports!$A:A,ImportsFromMasterData!$A:A,0))"
    End With

End Sub

Upvotes: 3

Stef Joynson
Stef Joynson

Reputation: 256

Selection.Autofill is not a very efficient way of achieveing this, but to get it to work adjust your AutoFill line to this:

Selection.AutoFill Destination:=Range(Range("B2"), Range("A2").End(xlDown).Offset(0, 1))

It was just the way you had constructed the Range used for destination that wasn't working. To debug this type of error try using your Immediate window and checking the address of the range you have created.

Upvotes: 1

Related Questions