Is there a MySQL % (approximation) equivalent in VBA?

Question

I want a VBA to look through a cell and determine whether the word XXX appears anywhere in it using the %. If yes I want VBA to change the entire cell to the word YYY.

Can anyone help me with this?

Answer 1

If you need to change only 1 cell's value, then use the InStr() function:

If InStr(1, Cells(1, 1).Value, "xxx")>0 Then Cells(1, 1).Value="xxx"

Obviously, replace Cells(1, 1).Value with your cell reference.

If you need to check multiple cells, then use the Range.Find method:

Dim firstAddress As String
With Worksheets(1).Range("a1:a500") 
    Set c = .Find("xxx", lookin:=xlValues) 
    If Not c Is Nothing Then 
        firstAddress = c.Address 
        Do 
            c.Value = "xxx" 
            Set c = .FindNext(c) 
        Loop While Not c Is Nothing And c.Address <> firstAddress 
    End If 
End With

Again, replace Worksheets(1).Range("a1:a500") with your own range reference.