时间戳

首页 > 解决方案 > 带变量的 Vlookup

excel - 带变量的 Vlookup

问题描述

我想在我的 vba 中使用 Vlookup 来搜索另一个选定的文件。我从录制的宏中复制的 vlookup。我更改了 Workbook 和 Worksheets 的名称，因为它每次都不一样。范围是一样的。我收到错误 438“对象不支持此属性或方法”。

With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        .Title = "Select BPM Report for previous month"
        .ButtonName = "OK"
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show

        oldbpm = .SelectedItems.Item(1) End With
     Workbooks.Open oldbpm

Dim Sheetstarybpm As Worksheet 
Set Sheetstarybpm = ActiveSheet

Dim countrowsoldbpm1 As Long
    countrowsoldbpm1 = Range("a10", Range("a10").End(xlDown)).Rows.Count

Workbooks("BPM-Tool.xlsm").Activate Worksheets("OU").Activate

Dim countrowsoldbpm As Long
    countrowsoldbpm = Range("a1", Range("a1").End(xlDown)).Rows.Count

    Range("D1:D1823").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(B2,'[" & oldbpm & "]" & Sheetstarybpm & "'!R10C2:R7000C101,100,FALSE),0)"
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D" & countrowsoldbpm)

标签： excelvba

解决方案

推荐阅读