If certain text appears in a cell

[fahimip]

I need to write an if function code so that if a certain sequence of letters appears in a cell then it carries out a task.
the only problem is that the sequence of text will be surrounded by other letters or numbers.


Any ideas Guys?

any help will be massively appreciated!

 

[Andrew Poulsom]

On a worksheet you can use eg:

=IF(ISNUMBER(SEARCH("abc",A1)),"Found","Not found")

In VBA you can use:

If Instr(4, Range("A1"), "abc") > 0 Then
   MsgBox "Found"
Else
   MsgBox "Not found"
End If

 

[fahimip]

Andrew thanks for your help, but could you please explain the VB code to me?
im pretty new at this.

And also will excel be able to find letters that are hidden between other letters in a cell, using this code?

 

[Richard Schollar]

Hi Andrew

I'm intrigued - why do you start the search at character position 4 in the above VBA example?

Richard

 

[Andrew Poulsom]

Hi Andrew

I'm intrigued - why do you start the search at character position 4 in the above VBA example?

Richard

Because I have fat fingers. It should of course be 1.

 

[Andrew Poulsom]

Andrew thanks for your help, but could you please explain the VB code to me?
im pretty new at this.

And also will excel be able to find letters that are hidden between other letters in a cell, using this code?

Yes to your second question.

What sort of explanation of the VB code do you want? It looks self explanatory to me.

 

[fahimip]

it doesnt seem to be working for some reason, heres what ive got;

range("b2").Select
If InStr(7, range("b2"), "sw") > 0 Then
Rows("1:1").Select
Selection.AutoFilter Field:=10, Criteria1:="=*shaft*", Operator:=xlAnd

[/quote]

 

[Andrew Poulsom]

This worked for me:

Sub Test()
    If InStr(1, Range("B2").Value, "sw") > 0 Then
        Rows("1:1").AutoFilter Field:=10, Criteria1:="=*shaft*", Operator:=xlAnd
    End If
End Sub

But why are you filtering only row 1?

 

[fahimip]

thanks andrew, i didnt realise it was case sensitive.
it works now.


thanks for your help