VBA "contains" or "like" statement?

[vio.coman]

Hey Guys,

I'm trying 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.
Whereas the Statement works for "SW" and "PM" as they are always situated in the beggining of the string, when it comes to checking whether the string contains "VERR" or "wegfall" the statement does not find/recognize them and therefore it does not return the specified "1".

Your suggestions are very much appreciated! many thanks ;-)

[] With ws1
With Range("AY7:AY" & Range("A" & Rows.count).End(xlUp).Row)
.Formula = "=IF(LEFT(M7,2)=""SW"","""",IF(LEFT(M7,2)=""PM"","""",IF((M7)="" * VERR * "","""",IF((M7)="" * wegfall * "","""",""1""))))"
'.Value = .Value
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="""""
.FormatConditions(1).Font.ColorIndex = 3
End With
End With [/]

[Andy Pope]

You could try this formula which uses FIND to check for subtext within text. use SEARCH if you are not bothered about case sensitivity.

=IF(OR(LEFT(M7,2)="SW",LEFT(M7,2)="PM",IFERROR(FIND("VERR",M7)>0,FALSE),IFERROR(FIND("wegfall",M7)>0,FALSE)),"","1")

For VBA you will need to double up on the quotes. as per your example.

[bulina2k]

Hello,

VBA supports Like operator : here
I also recommend using Instr VBA function

[antoka05]

You could use this formula:
=IF(OR(LEFT(M7,2)="SW",LEFT(M7,2)="PM",ISERR(SEARCH("VERR",M7))=FALSE,ISERR(SEARCH("wegfall",M7))=FALSE),"","1")

Regards,
Antonio

[vio.coman]

Perfect! Thanks a lot! ;-)