Like Operator Help

[daily106]

Can anyone help me with this code?
I am trying to do...

If a cell contains like "Special Word" , then do ABC...
Thank you.

While Rng <> ""

If Rng.Offset(-11, 0).Value = "Special Word" Then
Rng.Value = Rng.Offset(1, 0).Value & " Special Word"
Else
Rng.Value = Rng.Offset(1, 0).Value
End If
Set Rng = Rng.Offset(1)

Wend

 

[Oorang]

If LCase$(rng.Value) Like "*myword*" Then
    'Do Something
End If

Although if you are just making a simple check for a Substring inside a string there is a trick you can do with InStr(). InStr is supposed to return the number it found the substring in. So for example InStr("abcde","cd") would return 3. If you happen to know that any non-zero number converts to true, and a zero converts to false. And then top that off with the knowledge that the ByteVersion is faster... Then you can do this:

If InstrB(LCase$(rng.Value),"myword") Then 
    'Do Something
End If

Why would you do this? Well across a 20 or 30K cells you might notice a small speed improvement. Not really a have to just a nice to know.

 

[daily106]

Thank you! it's working perfectly. thank you for your detail explanation!!