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
HTML Code:
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:
HTML Code:
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