VBA "contains" or "like" statement?

[Captain Smith]

I have searched this board and I know somewhere there must be a post like this already, but I cannot find it.

I just want to write a simple VBA statement to identify if a cell contains a certain string, rather than the entire string being equal or not equal to a value.

Something like:
sub hello()
dim cell as range
For Each cell In Selection
if cell.contains (*pickle*) then msgbox ("I like pickles")
Next
End Sub

I know this is the wrong syntax but what is the right syntax?

Thanks!

 

[jindon]

sub hello1()
dim cell as range
For Each cell In Selection
if instr(1,cell,"pickle",1) then msgbox ("I like pickles")
Next
End Sub

sub hello2()
dim cell as range
For Each cell In Selection
if cell like "*pickle* then msgbox ("I like pickles")
Next
End Sub

 

[Captain Smith]

Thanks. I used the first method and it works great.
How would I change the first method to act on that cell if it does NOT
contain the word pickles?

Thanks again.

 

[jindon]

Thanks. I used the first method and it works great.
How would I change the first method to act on that cell if it does NOT
contain the word pickles?

Thanks again.

Depends...

Only negative

sub hello2()
dim cell as range
For Each cell In Selection
if not instr(1,cell,"pickle",1) then msgbox ("I don't like pickles")
Next
End Sub

both

sub hello1()
dim cell as range
For Each cell In Selection
if instr(1,cell,"pickle",1) then
msgbox ("I like pickles")
else
msgbox ("I don't like pickles")
end if
Next
End Sub

 

[Captain Smith]

I only care about the negative situation. The not does not seem to work for me.
Here is my complete code. It hides rows that do contain the n value, which
it shouldn't. Any ideas? Thanks

Sub filterthis()
Dim cell As Range
n = InputBox("Enter word or phrase you want to filter", "Enter")
For Each cell In Selection
If Not InStr(1, cell, n, 1) Then cell.EntireRow.Hidden = True
Next
End Sub

 

[jindon]

then try

Sub filterthis()
Dim cell As Range
n = InputBox("Enter word or phrase you want to filter", "Enter")
For Each cell In Selection
    cell.EntireRow.Hidden = (InStr(1, cell, n, 1) = 0)
Next
End Sub

 

[Captain Smith]

perfect! thank you

 

[BigShango]

Hi, I wonder if you can help

I have some code

If Range("Q" & VariableRow) Like VariableString Then

How could I adapt that to use the instr command?

 

[RoryA]

It would be:

If Instr(1, Range("Q" & VariableRow).Value, VariableString) > 0 Then

If you don't want a case sensitive comparison, then you can use:

If Instr(1, Range("Q" & VariableRow).Value, VariableString, vbTextCompare) > 0 Then

or convert both strings to the same case explicitly:

If Instr(1, UCase$(Range("Q" & VariableRow).Value), UCase$(VariableString)) > 0 Then

 

[BigShango]

Excellent.

Thanks