根据其他两个列的值选择一个范围

Question

我有两个列的O6：P范围，可评估J中的数据行并显示通过或失败。 O和P中的结果并不总是相同的。一个可以显示通过，另一个将失败。 我这里有的代码大部分都可以使用。除了它选择o或p等于通过的列中的范围。我需要它只能选择两个列通过的范围。有没有办法将其拆分，以便它仅选择J中的j和P中两个值的范围？

Dim lastrow As Long
  Dim xRg As Range, yRg As Range, nRg As Range, mRg As Range

    'Selecting range = to PASS
    With ShNC1
        lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
        Application.ScreenUpdating = False
        For Each xRg In .Range("O6:P" & lastrow)
            If UCase(xRg.Text) = "PASS" Then
                If yRg Is Nothing Then
                    Set yRg = .Range("J" & xRg.Row)
                Else
                    Set yRg = Union(yRg, .Range("J" & xRg.Row))
                End If
            End If
        Next xRg

    End With

    If Not yRg Is Nothing Then yRg.Select

Answer 1

您可以循环循环o，并使用偏移检查p：

Dim lastrow As Long
  Dim xRg As Range, yRg As Range, nRg As Range, mRg As Range

    'Selecting range = to PASS
    With ShNC1
        lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
        Application.ScreenUpdating = False
        For Each xRg In .Range("O6:O" & lastrow)
            If UCase(xRg.Text) = "PASS" And UCase(xRg.Offset(, 1).Text) = "PASS" Then
                If yRg Is Nothing Then
                    Set yRg = .Range("J" & xRg.Row)
                Else
                    Set yRg = Union(yRg, .Range("J" & xRg.Row))
                End If
            End If
        Next xRg

    End With

    If Not yRg Is Nothing Then yRg.Select

Answer 2

＃MS-Excel＃

输入或将此公式复制到要输出结果的空白单元中：

=INDEX(A2:A15,MODE(MATCH(A2:A15,A2:A15,0)))

提示：在此公式中：
A2：A15：是您想找到文本发生的最大次数的数据列表。