VLookup VBA 自动填充活动列

Question

我是 VBA 的新手，我正在尝试找到一种方法，使此公式根据 A 列中的查找值自动填充活动列，或者在活动单元格中返回公式，以便我可以自己自动填充它，因为目前它只返回结果

Private Sub CommandButton_d_Click()
    Dim filename As String 
    Dim fullRangeString As String
    Dim returnValue As Variant 
    Dim wb As Workbook 
    Dim ws As Worksheet
    Dim rng As Range 
    Dim article As Range 
    Dim rng1 As Range 
    Dim acell As Range
    On Error Resume Next
    Set acell = ActiveCell
    Set article = ActiveSheet.Cells(ActiveCell.Row, 1)
    filename = Application.GetOpenFilename( _
      FileFilter:="Excel Files (*.xlsx), *.xls", _
      Title:="Please select a file")
    Set wb = Application.Workbooks.Open(filename)
    Set ws = wb.Worksheets("Sheet1")
    Set rng = ws.Range("a1", _
      Range("a1").End(xlDown).End(xlToRight))
    Set rng1 = ws.Range("a2", "av2")
    acell = Application.WorksheetFunction.VLookup(article, rng, _
      Application.WorksheetFunction.Match(Me.ComboBox1, rng1, 0), False)
    wb.Close False
    UserForm1.Hide
End Sub

Answer 1

您至少不应该将VBA工作表函数用于VLOOKUP。
若要完成所需的操作，需要使用范围对象公式属性。

尝试类似操作：

acell.Formula = "=VLOOKUP(" & article.Value & "," & rng.Address(, , , True) & "," & _
    Application.WorksheetFunction.Match(Me.ComboBox1, rng1, 0) & ",FALSE)"

这不包括自动填充，因为我不确定你想做什么。

Answer 2

你好，我找到了解决方案。

这是最终代码。

细胞。公式 = "=VLOOKUP（" & article.地址（假，假）&"，"_ &"["&rng.Parent.Parent.Name &"]" &rng.Parent.Name &"'！" &RNG。地址 &"，" _ & Application.WorksheetFunction.Match（Me.ComboBox1， rng1， 0） & "，" _ &"假）"