如何在VBA中使用通配符单元格范围执行XLOOKUP?

huangapple go评论142阅读模式
英文:

How do I Perform an Xlookup within VBA with a wildcard cell range?

问题

对于一个VBA应用程序,我正在尝试在另一个工作表中查找通配符匹配(在变量之前或之后)。

Application.XLOOKUP(" * " & Mod_SelectColumns.PnPRefRange & " * ", Worksheets("工作表名称").Range("C3:C119"), Worksheets("工作表名称").Range("B3:B119"), "NOT FOUND", 2).

Mod_SelectColumns.PnPRefRange 是一个全局范围变量。

出于某种原因,我无法将" * "(通配符符号)与变量组合起来。

我该如何处理?

我尝试使用字符串范围作为占位符,这样可以工作。

英文:

For a VBA application I am trying to look for wildcard matches (before or after variable) in another sheet.

Application.XLOOKUP("*" & Mod_SelectColumns.PnPRefRange & "*", Worksheets("Worksheetname").Range("C3:C119"), Worksheets("Worksheetname").Range("B3:B119"), "NOT FOUND", 2). 

Mod_SelectColumns.PnPRefRange is a global Range variable.

For some reason I can't manage to combine the "*" (wildcard symbols) with the variable.

How can I manage that?

I tried using a string range as a place holder, this does work.

答案1

得分: 0

以下是代码的翻译部分:

原文:
Turns out you can loop through your range and perform XLOOKUPS that way. Here's an example sub statement using two tables on two seperate worksheets:

翻译:
原来你可以遍历你的范围并以这种方式执行XLOOKUP。以下是在两个不同工作表上使用两个表格的示例子程序语句:

英文:

Turns out you can loop through your range and perform XLOOKUPS that way. Here's an example sub statement using two tables on two seperate worksheets:

Sub FruitXlookup()

    Dim Lookuptring As String
    Dim i As Integer
    Dim SelectedCell As String
    Dim LookupCell As String
    
        For i = 2 To 9
        SelectedCell = "B" & CStr(i)
        LookupCell = "A" & CStr(i)
        Range(SelectedCell) = Application.XLOOKUP("*" & Range(LookupCell) & "*", Worksheets("LookupSheet").Range("A2:A5"), Worksheets("LookupSheet").Range("B2:B5"), "NOT FOUND", 2)
        Next i

 
End Sub

huangapple
  • 本文由 发表于 2023年5月25日 19:27:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/76331770.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定