Excel VBA 匹配具有 2 个变量的行-解网

问：

我有一个表，我需要根据两个变量从一行中返回一个值。如果变量的来源在电子表格中，我可以用 .我的问题是 var1 和 var2 来自 VBA 表单，而不是工作表中的单元格。当我尝试使用相同的方法在 VBA 中匹配这些变量时，VBA 将单独的字符串合并为一个并返回错误。 =INDEX(Col3, MATCH(var1 & var2, Col1 & Col2, 0))

在下表中，当给定中队 = 红色且呼号 = 五时，预期回报将是卢克·天行者。我得到的是我的脚本试图匹配 RedFive，由于一个标准与两个搜索范围，它会产生错误。

在表单上：
TextBox1 = = Squadron
TextBox2 = = Call Sign
Sheet1 是我原始数据中的 49,750 行数据（与星球大战无关），原始数据列的顺序不如此示例整齐。这是导入的数据。Sq.ValueCallSn.Value

rw = WorksheetFunction.Match(Sq.Value & CallSn.Value, Worksheets(Sheet1).Range("A:A") & Worksheets(Sheet1).Range("B:B"), 0)
Pilot= Worksheets(Sheet1).Cells(rw, 3)

结果需要在脚本中的其他进程中使用。（即在另一张桌子上查找飞行员的状态）

示例数据：

中队	呼号	飞行员
红	领导	加文·德雷斯
红	二	楔形安的列斯群岛
红	三	比格斯·暗光者
红	四	约翰·布兰农
红	五	卢克·天行者
红	六	杰克·波金斯
红	七	Elyhek Rue
红	八	扎尔·迪内斯
红	九	Nozzo Naytaan（诺佐·奈坦酒店）
红	十	塞隆·内特
红	十一	拉洛·苏雷尔
红	十二	帕克·奈科
金	领导	乔恩“荷兰人”范德
金	二	德克斯·提雷
金	三	伊万·韦莱纳
金	四	杰克·法瑞尔
金	五	戴维斯·克雷尔
金	六	布雷斯·马尔科
金	七	加兹多·伍尔科布
金	八	达奇·克里尔

Excel VBA 表单变量匹配

Sub tester()
    Dim rw
    rw = MatchedPilot("Red", "Two")
    If Not IsError(rw) Then
        Debug.Print "Matched pilot " & rw
    Else
        Debug.Print "No Match"
    End If
End Sub

'Match a pilot on "Squadron" and "Call Sign"
Function MatchedPilot(squad, callSign)
    Dim f As String
    f = "=INDEX(C:C,MATCH(""" & squad & """&""" & callSign & """,A:A&B:B,0),1)"
    Debug.Print f
    'use the worksheet version of Evaluate
    MatchedPilot = Worksheets("Sheet1").Evaluate(f)
End Function

0赞 user3598756 11/2/2023 #2

只是为了提供一些替代方法

1） D函数的使用

您可以在 Sheet1 中放置一些辅助单元格

并在代码中使用它们，如下所示：

Dim pilot As String
With Worksheets("Sheet1")
    .Range("G2").Value = Sq
    .Range("H2").Value = CallSn
    
    .Calculate
    pilot = .Range("I2").Value
End With

2） 自动过滤器的使用

With Worksheets("Sheet1")
    With .Range("A1:B" & .Cells(.Rows.Count, 1).End(xlUp).Row)
        .AutoFilter field:=1, Criteria1:=Sq
        .AutoFilter field:=2, Criteria1:=CallSn

        With .Resize(.Rows.Count - 1).Offset(1)
            If CBool(Application.Subtotal(103, .Columns(1))) Then
                pilot = .SpecialCells(XlCellType.xlCellTypeVisible).Cells(1, 3).Value
            End If
        End With
    End With
    .AutoFilterMode = False
End With

3）词典的使用

Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    Dim pilot As String
        With Worksheets("Sheet1")
            Dim cel As Range
            For Each cel In .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
                dict.Add cel.Value & "|" & cel.Offset(, 1).Value, cel.Offset(, 2).Value
            Next
            pilot = dict(Sq & "|" & CallSn)
        End With

这些在我的情况下都没有用。1）原始表格已导入，我不想在该页面上使用额外的单元格。2）返回值需要在以下代码中使用。该示例只是作为问题块的一段。筛选仅对人类有用，对以下代码无效。3）原始数据长度为49,750行。字典将是一个缓慢的方法，有这么多数据。

0赞 user3598756 11/4/2023

@KeeslerWaterWalker ， 1）我明白了。2）我不明白：什么会阻止过滤结果在代码的任何其他部分使用？3）字典非常快。你和他们合作过吗？4）对不起，如果我通过为您的问题提供可能的解决方案来打扰您

0赞 Keesler Water Walker 11/2/2023 #3

这就是最终为我工作的原因。我使用了 Tim Williams （https://stackoverflow.com/users/478884/tim-williams）解决方案的变体。

我对原始答案和我的 Office 365 版本遇到的问题是

我的版本不接受字符串中的“&”作为&。用 Chr（38）求解。
公式字符串中需要引号 Chr（34）来围绕要搜索的字符串。
我的版本没有选项。Worksheets(Sheet1).Evaluate

因为我的输入来自表单上的文本框，所以搜索到的数据被导入，并且这个块的输出在我的代码中被进一步使用，这就是我最终使用的......

Dim f As String

f = "=INDEX(C:C,MATCH(" & Chr(34) & Sq.Value & Chr(34) & Chr(32) & _ 
      Chr(38) & Chr(32) & Chr(34) & CallSn.Value & Chr(34) & _ 
      ", 'Sheet1'!A:A" & Chr(32) & Chr(38) & Chr(32) & "'Sheet1'!B:B,0),1)"

'The worksheet version of Evaluate didn't exist in my version of Excel, so went with the VBA version.

Debug.Print Evaluate(f)

Excel VBA 匹配具有 2 个变量的行

Excel VBA Match row with 2 variables

评论

评论

评论