在VBA中使用VLookup引用另一个Excel文件 [英] Using VLookup in VBA to reference another Excel file

问题描述

我正在尝试在VBA中编写一个引用另一个文件的VLookup表.这是我的目标的简单概述:

I'm trying to program a VLookup Table in VBA that references another file. Here is a simple outline of my goal:

• 在另一个Excel文件的A2单元格中查找值
• 从其他Excel文件的第2列中提取信息，并将其放在单元格B2中
• 继续移动到单元格A3并重复该过程，直到A列中不再有其他条目为止

这是我已经拥有的代码.我不断收到一个错误，指出无法获取WOrksheetFunction类的VLookup属性".我检查了其他引用该错误的帖子，但它们没有任何帮助.大家都在我的代码中看到错误吗?还是有人有更好的方法来完成这项任务?

Here is the code that I already have. I keep getting an error that says "Unable to get the VLookup property of the WOrksheetFunction class." I checked the other posts referencing that error but they were not of any help. Do you all see an error in my code? Or does anyone have a better way of accomplishing this task?

Sub SBEPlannerAdder()

Dim wbk As Workbook
Set wbk = Workbooks.Open("C:\Users\user\Documents\Support File\Planner.xlsx")

With Sheets("Sheet1")

    ' Selects the first cell to check
    Range("A2").Select
    Dim x As Variant
    x = wbk.Worksheets("Sheet1").Range("A1:C1752")

    ' Loops through all rows until an empty row is found
    Do Until IsEmpty(ActiveCell)

        Range(ActiveCell.Offset(0, 1) & ActiveCell.Row).Value = Application.WorksheetFunction.VLookup((ActiveCell.Column & ActiveCell.Row), x, 2, 0)
        ActiveCell.Offset(1, 0).Select
    Loop
End With

Call wbk.Close(False)
End Sub

推荐答案

打开工作簿时，它成为活动工作簿.看来您从未将控制权交还给目标工作簿.

When you open a workbook, it becomes the active workbook. It seems you were never passing control back to the target workbook.

Sub SBEPlannerAdder()
    Dim rw As Long, x As Range
    Dim extwbk As Workbook, twb As Workbook

    Set twb = ThisWorkbook
    Set extwbk = Workbooks.Open("C:\Users\user\Documents\Support File\Planner.xlsx")
    Set x = extwbk.Worksheets("Sheet1").Range("A1:C1752")

    With twb.Sheets("Sheet1")

        For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            .Cells(rw, 2) = Application.VLookup(.Cells(rw, 1).Value2, x, 2, False)
        Next rw

    End With

    extwbk.Close savechanges:=False
End Sub

请参见如何避免在Excel VBA宏中使用选择" 了解更多摆脱依赖选择和激活来实现目标的方法.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

这篇关于在VBA中使用VLookup引用另一个Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！