delete cell contents with certain words

[Marco1975]

hi,
delete cell contents with certain words

I'm trying to make a macro that in a column clears the contents of all the cells that don't contain the word "presso".

example :
in G5 : presso
in G6 : pressoxxx
in G7 : xxxx presso xxxx

I tried using this macro but doesn't work

Sub Prova()

Dim cella As Range

For Each cella In [G5:G10000]

If cella.Value <> "*presso*" Then cella.ClearContents

Next cella

End Sub

how can I solve the situation?
Thank you.

 

[Deepak]

Change this & check.


If not cella.Value like "*presso*" Then cella.ClearContents

Or

If not instr(cella.Value, "presso" ) Then cella.ClearContents

 

[Somendra Misra]

Hi,

Try Instr command:

Dim cella As Range

For Each cella In [G5:G10000]

If InStr(1, cella, "presso", vbTextCompare) > 0 Then cella.ClearContents

Next cella

Regards,

 

[Marco1975]

Thanks for your answers.
The solutions are all good ... there was only a problem in the Somendra solution:
this line:

 If InStr(1, cella, "presso", vbTextCompare) > 0 Then cella.ClearContents

I modified it so:

If InStr(1, cella, "presso", vbTextCompare) = 0 Then cella.ClearContents

and now it works.

Excellent I have 2 solutions.
Thanks.