首页 文章

在两个不同的单元格中查找相同的字符串，即Excel VBA

提问于
浏览 次
0

我试图在两个不同的单元格中找到一个字符串值，但无济于事 . 也许与搜索方向有关 . 代码在第一个单元格中找到字符串，但从不在第二个单元格中（单元格不相邻）？

请参阅代码摘录：

Dim aCell As Range, bCell, where1, where2
    lastarr = Worksheets("Locations").Range("C50").End(xlUp).Row - 1
    If lastarr < 1 Then GoTo end4
    Dim whatar()
    ReDim Preserve whatar(lastarr)
    where1 = .Cells(Application.Caller.Row, 4).Address _
             (RowAbsolute:=False, ColumnAbsolute:=False)
    where2 = .Cells(Application.Caller.Row, 9).Address _
             (RowAbsolute:=False, ColumnAbsolute:=False)
    For i = 1 To lastarr
        whatar(i) = Trim(Worksheets("Locations").Cells(i + 1, 3))
        Set aCell = .Range(where1).Find(What:=whatar(i), LookIn:=xlValues, _
            LookAt:=xlPart, MatchCase:=False)
        Set bCell = .Range(where2).Find(What:=whatar(i), LookIn:=xlValues, _
            LookAt:=xlPart, MatchCase:=False)
        rr = Application.Caller.Row
        If Not bCell Is Nothing Then Stop
        'If rr = 439 And i = 3 Then Stop
        If Not aCell Is Nothing Or Not bCell Is Nothing Then  
            gekry = True   '======= trigger!! =========
            'Stop
        End If
    Next
excel vba find

1 回答

  • 0

    我试图遵循你的代码和逻辑，但没有完全达到目的 . 如果要查找给定字符串的多个实例，可以采用我通常用于此类情况的样板代码：

    Dim strFirstFind As String
Dim cll As Range, rng As Range

With ActiveSheet.Range("A1:X100")
    Set cll = .Find("X", LookIn:=xlValues)
    If Not cll Is Nothing Then
        Set rng = cll
        strFirstFind = cll.Address
        Do
            Set rng = Union(rng, cll)
            .Range(cll.Address).Activate
            Set cll = .FindNext(cll)

        Loop While Not cll Is Nothing And cll.Address <> strFirstFind
    End If

    Debug.Print rng.Address

End With
    回复于

相关问题