Find & Replace

[jstevens]

I'm having a challenge finding and replacing text. The code I'm using is as follows and here is where I'm challenged.

I have a range where "D12345" is listed numerous times in the range and has some text after it. Such as: "D12345 MyDescription". I would like to do a search and replace on every cell where "D12345" is found but replacing it with "D12345 Legal". One point I need to make is that text "D12345" may be different in the range. It may be listed as "D12345 MyDescription01", "D12345 MyDescription02" or "D12345 MyDescription03". Either way the end result will be "D12345 Legal".

Code: Select all

Sub Find_Replace()
    Dim SFind As String
    Dim SReplace As String
    
    SFind = "D12345"
    SReplace = "D12345" & " Legal"

    Range("D5:D1676").Replace _
        What:=SFind, Replacement:=SReplace, _
    LookAt:=xlWhole, MatchCase:=False
End Sub

Thank you for your suggestion,
John

[HansV]

You can use wildcards in the search string: a question mark ? stands for a single arbitrary character, and an asterisk * for any number of characters. In this example, you want to find all cells whose value starts with "D12345", so you want to search for "D12345*". So change the line

SFind = "D12345"

to

SFind = "D12345*"

[jstevens]

Hans,

Your suggestion worked. I had to make a slight change in my code:

LookAt:=xlWhole to LookAt:=xlPart

Thank You,
John

[HansV]

I had to make a slight change in my code:

LookAt:=xlWhole to LookAt:=xlPart

That should only be necessary if there can be something before D12345 in the cell.