如何在多个条件下执行多数组查找?试图找到缺失的值 [英] How do I perform a multi-array lookup with multiple conditions? Trying to find missing values

问题描述

好的，这是我到目前为止的代码:

Okay, so this is the code I have so far:

Option Explicit

Sub Check_Transactions7()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim cws As Worksheet: Set cws = wb.Worksheets("Transactions")
    Dim dws As Worksheet: Set dws = wb.Worksheets("Transac Check")
    
    Dim sName As String: sName = dws.Range("D1").Value
    Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
    Dim srg As Range: Set srg = sws.Range("C4:G" & sws.Range("I1").Value)
    Dim Data As Variant: Data = srg.Value
        
    Dim i As Long, k As Long
    For i = 1 To UBound(Data, 1)
        If Application.Evaluate("(Transactions!P:P = " & Data(i, 1) & ") *" & _
           "(Transactions!L:L = " & Data(i, 4) & ") *" & _
           "(Transactions!Q:Q = " & Data(i, 5) & ")") = 0 Then
            k = k + 1
            Data(k) = Data(i)
        End If
    Next i
    
    dws.Range("N2").Resize(k, 5).Value = Data

End Sub

它在If语句的一个条件下很好地工作，但是当我尝试重新创建类似于该公式的内容时:

It works well with one condition on the If statement, but when I try to recreate something akin to this formula:

=ISNA(MATCH(1, (Transactions!P:P = C4)*(Transactions!L:L = F4)*(Transactions!Q:Q = G4), 0))

它给我一个类型不匹配的错误.

It gives me a type mismatch error.

如何调整If语句使其起作用?

How can I tweak the If statement to make it work?

谢谢:)

为澄清起见，If语句中显示类型不匹配错误.我也尝试过做COUNTIF，但它与我要重新创建的公式没有做相同的事情.该公式正在尝试查找缺失值.

As a clarification, the type mismatch error shows up in the If statement. I have also tried doing COUNTIF, but it doesn't do the same thing as the formula I'm trying to recreate. The formula is trying to find missing values.

推荐答案

离您的原始方法不太远...

Not going too far from your original method...

(未经测试，但您应该了解一般想法)

(untested but you should get the general idea)

Sub Check_Transactions7()
    'formula template
    Const f = "MAX((Transactions!P:P =<a1>)*(Transactions!L:L =<a2>)*(Transactions!Q:Q =<a3>))"
    
    Dim wb As Workbook, cws As Worksheet, dws As Worksheet
    Dim sName As String, sws As Worksheet, srg As Range, Data As Variant
    Dim i As Long, k As Long, frm, col As Long
    
    Set wb = ThisWorkbook ' workbook containing this code
    Set cws = wb.Worksheets("Transactions")
    Set dws = wb.Worksheets("Transac Check")
    
    sName = dws.Range("D1").Value
    Set sws = wb.Worksheets(sName)
    Set srg = sws.Range("C4:G" & sws.Range("I1").Value)
    
    Data = srg.Value
    k = 0
    For i = 1 To UBound(Data, 1)
        'build the formula with cell addresses, not values, to avoid
        '  having to figure out if quotes are needed...
        frm = Replace(f, "<a1>", srg.Cells(i, 1).Address())
        frm = Replace(frm, "<a2>", srg.Cells(i, 4).Address())
        frm = Replace(frm, "<a3>", srg.Cells(i, 5).Address())
        'be sure to evaluate the formula in the correct context
        '  Application.Evaluate uses the Activesheet, which may not
        '  be the one you want.
        If sws.Evaluate(frm) = 0 Then
            k = k + 1
            For col = 1 To UBound(Data, 2)
                Data(k, col) = Data(i, col)
            Next col
        End If
    Next i
    
    dws.Range("N2").Resize(k, 5).Value = Data

End Sub

这篇关于如何在多个条件下执行多数组查找?试图找到缺失的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！