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

[orecchibruno]

Hello to everyone I would understand what is my mistake in following code.

If I enable the MsgBox (SpecialReplace), I see that myResult produces the results that I expect but they will be lost and in Excel I see an error.

Function SpecialReplace(myRange As Range, myString As String) As Variant
Application.Volatile
    Dim myArray
    Dim myStringToSearch
    Dim counter As Integer
    Dim counter2 As Integer
    Dim myResult As String
    Dim space As String
    Dim stringfound As Boolean
    Dim previousResult As String
    space = ""
    
    myArray = Split(myString, " ")
    For counter = LBound(myArray) To UBound(myArray)
        myStringToSearch = myArray(counter)
        Dim returnAddress As Range
            Set returnAddress = Cells.Range(myRange.Address).Find(What:=myStringToSearch, LookIn:=xlFormulas, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                True, SearchFormat:=False)


            If returnAddress Is Nothing Then
                  stringfound = False
            Else
                  stringfound = True
            End If
            
            'now I search multi-words
            Do While stringfound = True
                previousResult = SpecialReplace
                If previousResult > "" Then
                    space = " "
                End If
                myResult = Cells(returnAddress.Row, (returnAddress.Column) + 1).Value + " {" + Cells(returnAddress.Row, (returnAddress.Column) + 2).Value + "}"
                SpecialReplace = SpecialReplace + space + myResult
'MsgBox (SpecialReplace)

                counter2 = counter + 1
                myStringToSearch = myStringToSearch + " " + myArray(counter2)
                'Dim returnAddress As Range
                  Set returnAddress = Cells.Range(myRange.Address).Find(What:=myStringToSearch, LookIn:=xlFormulas, LookAt _
                      :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                      True, SearchFormat:=False)


                  If returnAddress Is Nothing Then
                     stringfound = False
                     myStringToSearch = ""
                  Else
                     counter = counter + 1
                     myArray(counter) = myStringToSearch
                     SpecialReplace = previousResult
                     space = " "
                  End If
            Loop
    Next
End Function

I give you an example of what I would do. Suppose in a cell I have following text

lady beetle panthera tigris panthera

And I would split them into an Array using the "space" and I would search my words in a Cell Range like this:

beetle
lady
lady beetle
panthera tigris
panthera 
bla bla

If there is "Lady beetle" I don't want to get "lady" and "beetle" so I've created a While but something is wrong

Please explain me where is my mistake, this is my first VBA script
Thanksss