CODEWITHSUNDEEP
vbaexcel
Holly Kat
Holly Kat

Reputation: 51

Search Cell Value by Part, Replace Entire Cell Value

I would like to search all cells on my named worksheet (AdjacencyData) to see if they contain the value "NOT FOUND IN MASTER CATEGORY LIST." My cells contain long strings, and hidden somewhere with in them could be the phrase. I want to search the entire cell to see if that phrase is anywhere within, and then if the cell contains that phrase anywhere within, replace the entire value with the cell with a new phrase "LOCATION UNSURE" Is there any way to do this? I have some guesses, but I am not very good with the find and replace option.

find = "NOT FOUND IN MASTER CAT LIST"
f = AdjacencyData.Cells.find(What:=find, lookat:=xlPart)
If f Is Nothing Then
    Else

Don't know what to do next, this partial code is my only idea!

Upvotes: 2

Views: 363

Answers (1)

Chrismas007
Chrismas007

Reputation: 6105

You will want to invest in the .Range.FindNext function:

FindMe = "NOT FOUND IN MASTER CAT LIST"
With Sheets("AdjacencyData").Range("Defined Range") 
    Set f = .Find(What:=FindMe, lookin:=xlValues) 
    If Not f Is Nothing Then 
        firstAddress = f.Address 
        Do 
            f.Value = "LOCATION UNSURE" 
            Set f = .FindNext(f) 
        Loop While Not f Is Nothing And f.Address <> firstAddress 
    End If 
End With

Upvotes: 1

Related Questions