PDA

View Full Version : [SOLVED] Replace Exact String - Array



dj44
11-27-2017, 03:08 AM
Good Morning folks,

any one know how i can search and replace for exact only,

my array has

FCPH and CPH so i get that replaced twice

as FCPH contains CPH






Sub Replace_Exact()


oSearch = Array("Apple", "Pear", "FCPH", "CPH")

For i = LBound(oSearch) To UBound(oSearch)

For Each ocell In ThisWorkbook.Worksheets("AA").Range("A1:A100").Cells

ocell.Replace What:=oSearch(i), Replacement:="test"


Next ocell
Next i

End Sub


how can i limit the search to exact only ?

snb
11-27-2017, 03:38 AM
Is F1 in your VBEditor locked/absent ? ;)

The second loop is redundant.

YasserKhalil
11-27-2017, 03:41 AM
Hello
Try this line instead

oCell.Replace What:=oSearch(i), Replacement:="test", LookAt:=xlWhole

mana
11-27-2017, 03:42 AM
Option Explicit

Sub Replace_Exact()
Dim oSearch
Dim i As Long

oSearch = Array("Apple", "Pear", "FCPH", "CPH")

For i = LBound(oSearch) To UBound(oSearch)
ThisWorkbook.Worksheets("AA").Range("A1:A100").Replace _
What:=oSearch(i), Replacement:="test", _
LookAt:=xlWhole, MatchCase:=True, matchbyte:=True
Next i

End Sub



マナ

snb
11-27-2017, 04:13 AM
@mana

Why not ?


Sub Replace_Exact()
For each it in Array("Apple", "Pear", "FCPH", "CPH")
ThisWorkbook.sheets("AA").Range("A1:A100").Replace it, "test", 1
Next
End Sub

dj44
11-27-2017, 04:14 AM
Thank you Yasser and Mana,

LookAt:=xlWhole, MatchCase:=True, matchbyte:=True,

Yes this is the line that I was looking for :doh:

Have a great day !

dj44
11-27-2017, 04:16 AM
Thank you snb,
let me experiment with this version as well