Let assume I have an array something like this:
Dim Arr() As Variant
arr(0)= "Text<&>data"
arr(1)= "<&>recombining"
arr(2)= "that libraries<&>"
arr(3)= "<&>professional<&>user "
I would like to search inside the values and find all <&> and then replace them with and.
My efforts by .find were unsuccessful. :(
You could avoid loop turning array into a string with Join(), making the replacement and finally turning it back to an array with Split() (Not tested):
Dim Arr As Variant
Arr = Array("Text<&>data", _
"<&>recombining", _
"that libraries<&>", _
"<&>professional<&>user ")
Arr = Split(Replace$(Join(Arr, "|"), "<&>", "and"), "|")
Use Replace$. Not sure if you wanted to add any whitespace? I use constants as you have fixed bounds so no calls to UBound and LBound when looping the array. Also, use typed function Replace$ as more efficient.
Option Explicit
Public Sub test()
Const START_POINT As Long = 0
Const END_POINT As Long = 3
Dim Arr(START_POINT To END_POINT) As Variant, i As Long
Arr(0) = "Text<&>data"
Arr(1) = "<&>recombining"
Arr(2) = "that libraries<&>"
Arr(3) = "<&>professional<&>user "
For i = START_POINT To END_POINT
Arr(i) = Replace$(Arr(i), "<&>", "and")
Next
For i = START_POINT To END_POINT
Debug.Print Arr(i)
Next
End Sub
