If Statement

[Guest]

Hi,

I am a novice with VBA and am trying to put an if statement which detects
whether the cell contains the word "total" within the cell (there will be
text before and after it).

I have tried :
If ActiveCell.value = "*total*" Then
but this doesn't work.

Many Thanks in advance
Pete

 

[Don Guillett]

try

If InStr(ActiveCell, "total") > 0 Then MsgBox "its there"

 

[Don Guillett]

Or this to account for case
If InStr(ActiveCell, UCase(TOTAL)) > 0 Then MsgBox "its there"

 

[Tom Ogilvy]

The first will find "total" and the second will find nothing (since the
variable TOTAL) is uninitialized, but assuming you meant

If InStr(ActiveCell, UCase("TOTAL")) > 0 Then MsgBox "its there"
it would find only TOTAL

to do a case insensitive search

You probably meant
If InStr(Ucase(ActiveCell), "TOTAL") > 0 Then MsgBox "its there"

or better:

if Instr(1,ActiveCell,"total",vbTextCompare) then
MsgBox "its there"
End If

 

[Don Guillett]

this is what I meant
If InStr(Ucase(ActiveCell), "TOTAL") > 0 Then MsgBox "its there"

 

[Guest]

That works great !
Thanks for your help guys.
Pete