*expression* - how to find surrounded string

[Saladin Andreas]

I would like to hide all rows that do not match a criteria in a specific
column. The criteria though is sometimes "hidden" in a text like
blablakeywordblabla - in this case the keyword is given and should be
identified regardless whether there is blabla around. The kleene stern method
*keyword* does not work, I tried already the following:
If Cells(x, 11).Value = "*erledigt*" Then
Cells(x, 11).EntireRow.Hidden = True
End If
Does somebody know how I could do this?
Thanks

 

[Sheeloo]

Test with Instr() method...
example
Sub t()
tmp = Cells(1, 1)
If InStr("keyword", tmp) Then
Debug.Print "Found"
Else
Debug.Print "Not found"
End If
End Sub

Convert to UPPER if case does not matter...

 

[Bernie Deitrick]

Use Like as the comparison, not =

If Cells(x, 11).Value Like "*erledigt*" Then

Though your comparison would work in certain worksheet functions...

HTH,
Bernie
MS Excel MVP

 

[Rick Rothstein]

Consider using the Like operator instead of the "equal" operator...

If Cells(x, 11).Value Like "*keyword*" Then

 

[Rick Rothstein]

Two things...

1) Uh, you have your arguments reversed...

If InStr(tmp, "keyword") then

2) You don't need to use the UCase function (not the UPPER function; that is
a worksheet function, not a VB one) to perform an case-insensitive search,
InStr can do that via its optional arguments...

If InStr(1, tmp, "keyword", vbTextCompare) then

 

[Saladin Andreas]

Thank you very much

sometimes it is so easy, with the like operator it just works - wonderful