*expression* - how to find surrounded string

  • Thread starter Thread starter Saladin Andreas
  • Start date Start date
S

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
 
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...
 
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
 
Consider using the Like operator instead of the "equal" operator...

If Cells(x, 11).Value Like "*keyword*" Then
 
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
 
Thank you very much

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