时间戳

首页 > 解决方案 > 具有可变查找值的过滤范围中的 Vlookup，可变查找范围 VBA

excel - 具有可变查找值的过滤范围中的 Vlookup，可变查找范围 VBA

问题描述

我正在尝试将 Vlookup 应用于具有变量查找值（根据行号更改）和变量查找范围（来自用户浏览的工作簿）的过滤范围。但是，运行代码后的公式栏将公式显示为：-

=IFERROR(VLOOKUP(@Sri Lanka15-@a_One-@Time Base Rent,'[C_Rent Report_25082020.xlsx]Sheet 1'!$J$1:$N$968,4,0)," ")

我不确定这些“@”符号是从哪里来的。此特定行的查找值为：-Sri Lanka15-a_One-Time Base Rent。

以下是代码：-

    Dim LR As Long   ' Defined as Last row in source file
    Dim nlr As Long   'Defined as Last row in Macro Workbook where vlookup is applied
    Dim Filename As String
    Filename = Application.GetOpenFilename(FileFilter:="All Files(*.xls; *.xlsx; *.csv),*xls,*.xlsx, *csv", Title:="Select File To Be Opened")
    Workbooks.Open Filename:=Filename
    sourcefile = Dir(Filename)
    With ActiveSheet
       Range("A1:AQ" & nlr).AutoFilter Field:=25, Criteria1:="One-Time Base Rent"
       For Each cell In Range("AA2:AA" & nlr).SpecialCells(xlCellTypeVisible)
            lookupvalue = Cells(cell.Row, "Z").Value
            cell.Formula = "=IFERROR(VLOOKUP(" & lookupvalue & ",'[" & sourcefile & "]Sheet 1'!$J$1:$N$" & LR & ",4,0),"" "")"    ' The problem seems to be here in lookup value as rest are appearing as fine in formula
       Next
             
    End With

因为我需要在此之后应用后续过滤器。我想将查找值保持为变量。

我也尝试过 WorksheetFunction.Vlookup，但我不确定如何从用户在工作表函数中选择的文件中定义范围

任何帮助都非常感谢！谢谢

标签： excelvba

解决方案

请尝试更换

cell.Formula = "=IFERROR(VLOOKUP(" & lookupvalue & ",'[" & sourcefile & "]Sheet 1'!$J$1:$N$" & LR & ",4,0),"" "")"

和

cell.Formula = "=IFERROR(VLOOKUP(" & cells(cell.Row, "Z").Address & ",'[" & sourceFile & "]Sheet 1'!$J$1:$N$" & lr & ",4,0),"" "")"

推荐阅读