I am trying to write a macro. I am at the part where my reference ID can be found multiple times in a column. It is in a text box but the cell will only have the reference ID once. That reference ID can match up to multiple rows. I have the rest of the macro working fine. It's when I try to use find or findnext that I am messing up. I have a counter that looks up how many times my reference ID showed up in the column range I:I which I have assigned to a range variable. I subtract from that counter in a do while loop and use find next in it. The problem is after finding the first two unique values it just gets stuck on the second value it found and keeps populating that. This is my code so far.

    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

CodePudding user response：

Do Loop vs 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

CodePudding user response：

Here is a good example of how to use Range.FindNext.

Excel will use the previous Range.Find LookIn and LookAt values. For this reason they need to be set to avoid any kind of ambiguity.

FindCells

I wrote this function to return the Union of all the found cells.

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

Usage

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

Data

Result