在Range.Find VBA中使用Or函数 [英] Using an Or function within Range.Find VBA

问题描述

可以在range.find中放置和OR函数吗？我有这个功能，但是我想要查找2个值中的任一个

Is it possible to put and OR function within a range.find? I have this function but I'd like it to look for either of 2 values

With Worksheets("Term").Range("A:A")

        Set rng = .Find(What:=Worksheets("Term and Discipline").Range("K7"), _
                       After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        Application.Goto rng, True
End With

在你提供它正在寻找的行中，我试图放入一个OR语句，但当我尝试运行它时，Excel会让我生气

In the line where you provide what it's looking for I've tried to put in an OR statement but Excel gets mad at me when I try to run it

推荐答案

我认为最近的等价物将是并行执行搜索（有效地），并使用 MIN（）来选择找到的第一个单元格。

I suppose the nearest equivalent would be to perform the searches in parallel (effectively) and use MIN() to select the first cell found.

Sub FindingNemor()
    Dim rngFoo As Range
    Dim rngBar As Range

    Set rngFoo = Cells.Find(What:="foo", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    Set rngBar = Cells.Find(What:="bar", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)

    If Not rngFoo Is Nothing And Not rngBar Is Nothing Then
        Range("A1").Cells(Application.Min(rngFoo.Row, rngBar.Row)).Select
     End If
End Sub

它需要额外的检查是否只有rngFoo或rngBar中的一个是Nothing。

It requires extra checks in case only one of rngFoo or rngBar is Nothing.

已添加检查 Nothing

Added Checking for Nothing-ness makes it a little messier:

Sub FindingNemor()
    Dim rngFoo As Range
    Dim rngBar As Range

    Set rngFoo = Cells.Find(What:="foo", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    Set rngBar = Cells.Find(What:="bar", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)

    If Not rngFoo Is Nothing And Not rngBar Is Nothing Then
        Range("A1").Cells(Application.Min(rngFoo.Row, rngBar.Row)).Select
    ElseIf rngFoo Is Nothing Then
        If rngBar Is Nothing Then
            MsgBox "Neither found."
        Else
            Range("A1").Cells(rngBar.Row).Select
        End If
    Else
        Range("A1").Cells(rngFoo.Row).Select
    End If
End Sub

这篇关于在Range.Find VBA中使用Or函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！