syntax problem with a While nested into a Loop (for an Array)

[orecchibruno]

Hey guys, thank you again!

Finally I've correct the code:

Function SpecialReplace(myRange As Range, myString As String) As Variant
Application.Volatile
    Dim myArray
    Dim myArray2
    Dim currentString As String
    Dim stringToSearch As String
    Dim counter As Integer
    Dim counter2 As Integer
    
    Dim myPos As Integer
    Dim tot As Integer
    Dim mySeparator As String
    Dim stringfound As Boolean
    Dim finalResult As String
    Dim result As String
    Dim myResult As String
    Dim finalTextToCopy As String
       
    myArray = Split(myString, " ")
    For counter = LBound(myArray) To UBound(myArray)
        'start the search
        currentString = ""
        tot = 0
        myPos = 0
        For myPos = 0 To tot
            If currentString = "" Then
                currentString = myArray(counter)
            End If
            If counter < UBound(myArray) Then
                counter2 = counter + 1
                stringToSearch = currentString + " " + myArray(counter2)
            Else
                stringToSearch = currentString
            End If
            Call performMySearch(stringToSearch, result, myRange)
            'se la trovo
            If finalResult > "" Then
                mySeparator = "|"
            End If
            If result > "" Then
                'for a Loop behaviour that I didn't expect, it's worthless to increase Target Loop instead as this:
                'counter = counter + 1
                'tot = tot + 1
                'finalResult = finalResult + mySeparator + stringToSearch
                'so, I dot this and I move backwards in Previous loop:
                myArray(counter2) = stringToSearch
                If counter = UBound(myArray) Then
                    finalResult = finalResult + mySeparator + stringToSearch
                End If
                myPos = tot
            Else
                finalResult = finalResult + mySeparator + currentString
            End If
        Next myPos
        'MsgBox (finalResult)
    Next counter
    'SpecialReplace = finalResult 'this is a new parsed Array using a pipe separator "|"
    
    mySeparator = ""
    myArray2 = Split(finalResult, "|")
    For counter2 = LBound(myArray2) To UBound(myArray2)
        'Dim returnAddress As Range
            Set returnAddress = Cells.Range(myRange.Address).Find(What:=myArray2(counter2), LookIn:=xlFormulas, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                True, SearchFormat:=False)
            
            If finalTextToCopy > "" Then
                mySeparator = " "
            End If
            If returnAddress Is Nothing Then
                finalTextToCopy = finalTextToCopy + mySeparator + "€€€"
            Else
                myResult = returnAddress.Offset(, 1) & " " & returnAddress.Offset(, 2)
                finalTextToCopy = finalTextToCopy + mySeparator + myResult
            End If
    Next counter2
    SpecialReplace = finalTextToCopy
End Function
Function performMySearch(ByRef stringToSearch, ByRef result, ByVal myRange)
    Dim Space As String
    Dim returnAddress As Range
    Set returnAddress = myRange.Find(What:=stringToSearch, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False)
        
    If returnAddress Is Nothing Then
        result = ""
    Else
        result = returnAddress
    End If
End Function

I tried using the While but something was wrong, maybe I've encountered following problem that thanks to the Debugger I learned why.
I see that is not allowed to change the End of Loop while is still in progress:

For myPos = 0 To tot
    'do something
    'for some reason encrease target Loop, but it doesn't work
    If result > "" Then
        tot = tot + 1
    EndIf
Next myPos

If someone has a solution to post pone the end of the loop, I'll appreciate to learn it

Thanks again