.FindNext keeps returning to the first match, instead of the next-CodePudding

I have a SourceFile.xlsm that contains an X number of field definitions and their contents:

I want to put the contents of these fields into a TargetFile.xlsx, that may contain 0 or more of those field definitions:

The expected end result would be this:

But the actual end result is always this:

And that is because this line in the code below:

Set source_range = sourceSheet.Cells.FindNext(source_range)

always keeps coming back to the first occurrence (cell B5, containing "[Field 1]"), instead of the next (cell B6, containing "[Field 2]"):

Function CopyFromSourceToTarget()
Dim sourceWB As Workbook
Dim targetWB As Workbook

Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet

Dim source_range As Range
Dim target_range As Range

Dim FirstFound_source As String
Dim FirstFound_target As String


Set sourceWB = ActiveWorkbook
Set targetWB = Workbooks.Open("C:\TEMP\TargetFile.xlsx")

For Each sourceSheet In sourceWB.Worksheets
    Set source_range = sourceSheet.Cells.Find("[", LookIn:=xlValues)
    
    If Not source_range Is Nothing Then
        FirstFound_source = source_range.Address
        
        Debug.Print source_range.Value
        
        Do
        
            sourceWB.Activate
            source_range.Select
            
            For Each targetSheet In targetWB.Worksheets
                Set target_range = targetSheet.Cells.Find(source_range.Value, LookIn:=xlValues)
                If Not target_range Is Nothing Then
                    FirstFound_target = target_range.Address
                    Do
                        target_range.FormulaR1C1 = CStr(source_range.Offset(0, 1).Value)
                        Set target_range = targetSheet.Cells.FindNext(target_range)
                        If target_range Is Nothing Then Exit Do
                    Loop Until target_range.Address = FirstFound_target
                End If
            Next
            
            Set source_range = sourceSheet.Cells.FindNext(source_range)
            
            Debug.Print source_range.Value
        Loop Until source_range.Address = FirstFound_source
        
    End If
Next
End Function

I've tried several options, but all to no avail. Hopefully, someone here can help me along, because this seemingly very simple issue is driving me nuts.

CodePudding user response：

Instead of this line:

Set source_range = sourceSheet.Cells.FindNext(source_range)

try this line:

Set source_range = sourceSheet.Cells.Find(What:="[", After:=source_range, LookIn:=xlValues)

I'd also add some more options to the Find like LookAt:= xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False, but it might not be necessary. Up to you.