时间戳

首页 > 解决方案 > VBA - 从多个条件中查找行

excel - VBA - 从多个条件中查找行

问题描述

我以前也走过这条路。匹配功能在我使用单个标准时有效，但我无法使用两个标准正确使用它。

我当前的程序在 NR_Qualtrics 页面上查找 CaseID 和电子邮件的匹配项。因为这些列可能会根据我获得的每个文档而有所不同，所以我有一个程序可以识别行并返回 CaseID 和 Email 所在的范围。

我在这个函数中做的下一件事是遍历 NonResidential 工作表中的每一行，试图在 CaseID 和 Email 上找到匹配项。我需要该函数返回找到匹配项的行并在 O 列中报告该行。目前，我仍在尝试查找匹配的列。我在调试时不断收到错误 2015 和错误 2029。当我手动查看匹配项时，我确信匹配项存在。我不确定我的程序做错了什么。你能帮忙吗？ 

Sub NonResFindMultipleProviders()
    'This function finds any duplicate Case IDs for NR providers where they had surveys
    'This function needs to run before NonRes_ChkSurveyRcd
    'If there are duplicates, it will indicate that in the Notes Column (N)
    'If a duplicate exists, then it must match on Email and CaseID and show the value of Yes-NR_Qualtrics Row XX/No in Survey Recieved (Column O)
    Application.ScreenUpdating = False

    Dim r, lastRow, rowMatch As Long
    Dim colCaseID, colEmail, colResponseID As Long
    Dim rngCaseID, rngEmail, rngResponseID As Range
    Dim valEmail, valCaseID As String
    Dim result As Variant
    Dim shtNR As Worksheet
    Dim shtQNR As Worksheet

    Sheets("NonResidential").Select
    lastRow = getLastRow

    Range("A2").Select

    Set shtNR = ThisWorkbook.Sheets("NonResidential")
    Set shtQNR = ThisWorkbook.Sheets("NR_Qualtrics")

    colCaseID = FindColHeaderWText("NR_Qualtrics", "ExternalDataReference")
    Set rngCaseID = Worksheets("NR_Qualtrics").Columns(colCaseID)
    colEmail = FindColHeaderWText("NR_Qualtrics", "EmailAddress")
    Set rngEmail = Worksheets("NR_Qualtrics").Columns(colEmail)
    'colResponseID = FindColHeaderWText("NR_Qualtrics", "ResponseID")
    'Set rngResponseID = Worksheets("NR_Qualtrics").Columns(colResponseID)

    'The Notes field(column N) shows the duplicates.  Find matches on CaseID AND Email
    ''Not (IsError(Application.Match(Cells(r, 1).Value, rng, 0))) And
    For r = 2 To lastRow
        valCaseID = Cells(r, 1).Value   'Column A (1) has CaseIDs
        valEmail = Cells(r, 12).Value   'Column L (12) has emails

        result = shtNR.Evaluate("MATCH(" & valCaseID & "&" & valEmail & ",rngCaseID&rngEmail,0)")
        If (Not IsError(result)) Then    'Mark only if Notes - Column N (13) is marked with a duplicate - Need to put this in next.  RN, just evaluate everything
            Cells(r, 15).Value = "Yes"  'Column O (14) Survey Recieved marked with Yes
        End If
    Next r

    'Cleanup - Remove the words duplicate
    'Columns("N:N").Select
    'Selection.Replace What:="Duplicate", Replacement:="", LookAt:=xlWhole, _
    '    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    '    ReplaceFormat:=False

    Application.ScreenUpdating = True

End Sub

标签： excelvba

解决方案

这是一个基于 3 个条件进行匹配的示例。尝试将您的范围和标准放入其中：

Sub MatchMultipleCritera()

    ' Cells containing the values to match
    Dim criteria1 As Range, criteria2 As Range, criteria3 As Range
    Set criteria1 = Range("A1")
    Set criteria2 = Range("B1")
    Set criteria3 = Range("C1")


    ' Ranges containing the values to be checked against the match values above.
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Set rng1 = Range("Table1[Item]")
    Set rng2 = Range("Table1[Active]")
    Set rng3 = Range("Table1[Quanitity2]")

    MsgBox "Row " & Evaluate("=MATCH(1,(" & criteria1.Address & "=" & rng1.Address & ")*(" & criteria2.Address & "=" & rng2.Address & ")*(" & criteria3.Address & "=" & rng3.Address & "))")
End Sub

在这个例子中：

  • 单元格 A1、B1 和 C1 包含我匹配的值。
  • 我正在查看一个Excel.ListObject名为“Table1”的表 ()，其中包含“Item”、“Active”和“Quantity”列。
  • rng1检查criteria1, rng2forcriteria2等中的值。
  • 结果是行号。

这是使用数组公式对这篇 ExcelJet 文章的 VBA 修改。该Evaluate函数默认将公式计算为数组公式

推荐阅读