vba - 填写VBA

Question

我想插入一个新列，粘贴一个公式，然后将该公式填充到 A 列中数据的末尾。除了填充过程之外，我已经完成了所有工作。当它到达偏移量时它就会失败。有没有办法在 VBA 中做到这一点？任何帮助将不胜感激!

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

Answer 1

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