VBA Find and replace but only certain cells

[tonywatsonhelp]

Hi Everyone,

I have this bit of code that finds a word then removes other parts from the sentance in the cell, but I need it to find all of them not just one.

here is the code:

Sub Testit1()
Set rngX11 = ActiveSheet.Cells.Find("Structure", lookat:=xlPart)
rngX11.Replace What:="""", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
rngX11.Replace What:=".", Replacement:="", lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End Sub

So as you can see it looks for the word

"Structure" anywhere in the sheet, and when it finds it it replaces all "" with nothing and all . with nothing
but I have more than one word of

"Structure" and I need it to do this with everyone.
I can't just use replace on the sheet as there is data that needs the . and the ""

the cell will always begin with the word

Structure if that helps.

Please help if you can

Thanks

Tony​

​

_{^{<strike>
</strike>}}​

 

[Akuini]

Maybe this:

Sub testtt()
Dim sAddress As String, rngX11 As Range

Set rngX11 = ActiveSheet.Cells.Find("Structure", lookat:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False)
If Not rngX11 Is Nothing Then
    sAddress = rngX11.Address
    Do
       Set rngX11 = Cells.FindNext(rngX11)
           rngX11 = Replace(Replace(rngX11, """", ""), ".", "")

    Loop While rngX11.Address <> sAddress
End If
End Sub

 

[tonywatsonhelp]

Excellent work perfect firt time, thank you very much Akuini
Tony

 

[Akuini]

You're welcome & thanks for the feedback