VBA 中的查找函数不是查找下一个

Question

我正在尝试写一个宏。我所在的部分是我的参考 ID 可以在一列中多次找到。它位于文本框中，但单元格只会有一次参考 ID。该参考 ID 最多可以匹配多行。我的宏的其余部分工作正常。当我尝试使用 find 或 findnext 时，我就搞砸了。我有一个计数器，用于查找我的参考 ID 在我分配给范围变量的列范围 I:I 中出现的次数。我在 do while 循环中从该计数器中减去并在其中使用 find next 。问题是在找到前两个唯一值后，它只会卡在找到的第二个值上并继续填充该值。这是我到目前为止的代码。

    If Counter > 0 Then
        
        'Uses cellFound as a range set to our original find
        
        Set cellFound = DidIFind.Find(valueToSearch)
        
        'Keeps going till we find all the values that the counter ticks down
        
        Do While Counter > 0

            Set cellFound = cellFound.FindNext
            Set cellFound = DidIFind.Find(valueToSearch)
            'Marks row position of new duplicate find so we can add to ID string
            
            Rw = cellFound.Row
            ConcatString = ConcatString & ", " & lookUpSheet.Cells(Rw, Col).Value
            
            'Counter ticks down by 1
            Counter = Counter - 1
        Loop
    End If

Answer 1

Do Loop 与 FindNext

' This is usually handled without any counter but no harm done.
' I'm assuming 'Counter = Application.CountIf(DidIFind, valueToSearch)'.
If Counter > 0 Then
    ' if all this is in a loop you need:
    'ConcatString = ""
    Set CellFound = DidIFind.Find(valueToSearch)
    Do
        Rw = CellFound.Row
        ConcatString = ConcatString & ", " & lookUpSheet.Cells(Rw, Col).Value
        Counter = Counter - 1
        If Counter = 0 Then Exit Do
        Set CellFound = DidIFind.FindNext(CellFound)
    Loop
    ' remove leading delimiter (", ")
    ConcatString = Right(ConcatString, Len(ConcatString) - 2)
End If

Answer 2

下面是如何使用 Range.FindNext 的一个很好的示例。

Excel 将使用之前的 Range.Find LookIn 和 LookAt 值。因此，需要对它们进行设置以避免任何形式的歧义。

FindCells

我编写了这个函数来返回所有找到的单元格的并集。

Function FindCells(Source As Range, What As Variant, Optional LookIn As XlFindLookIn = xlValues, Optional LookAt As XlLookAt = xlWhole) As Range
    Dim Map As New Collection
    Dim Target As Range
    Dim FirstCellAddress As String
    Dim AllCells As Range
    
    Set Target = Source.Find(What:=What, LookIn:=LookIn, LookAt:=LookAt)
    If Target Is Nothing Then Exit Function
    
    FirstCellAddress = Target.Address
    Set AllCells = Target

    Do
        Set Target = Source.FindNext(Target)
        If FirstCellAddress <> Target.Address Then
            Set AllCells = Union(AllCells, Target)
        End If
    Loop While FirstCellAddress <> Target.Address
    
    Set FindCells = AllCells
End Function

使用

Sub Usage()
    Const What As String = "Bangalore"
    Const Col As Long = 3
    Dim Source As Range
    Set Source = Sheet1.UsedRange.Columns(1)
    
    Dim Result As Range
    Set Result = FindCells(Source, What)
    
    If Not Result Is Nothing Then
        
        Dim Cell As Range
        Dim Text As String
        
        For Each Cell In Result
            Text = Text & ", " & Cell.EntireRow.Columns(Col).Value
        Next
        Text = Mid(Text, 3)
        Debug.Print Text
    End If
    
End Sub

数据

结果