VB IF Statement dependant on contents of Cell

[flicky_longcartridge]

Hi Experts,

I am attempting to write an if statement that will read the contents of a cell, and based on this act appropriately.

Is this possible? It only needs to look for two conditions, so as follows:

If cell C4 Contains the word "evening" (

D5 = 5 + 2

)

Else If cell C4 Contains the word "morning" (

D5 = 5 + 1

)

End If

Has anyone done something like this before. An example of the contents of this cell for each condition is as follows:

OBLOG for the evening run of Mon 01/09/08
OBLOG for the morning run of Mon 01/09/08

 

[MrKowz]

Pretty sure this will work for you.

If Not IsError(WorksheetFunction.Find("evening", Range("C4").value)) Then
    Range("D5") = 5 + 2
ElseIf Not IsError(WorksheetFunction.Find("morning", Range("C4").value)) Then
    Range("D5") = 5 + 1
End If

 

[RoryA]

Did you mean D5 = 5 + 2 (i.e. 7) or D5 = D5 + 2?

 

[Jonmo1]

look at the Instr Function (works the same as find)

Instr(startposition,string searched,string searched for, compareoption)

Returns the position # the string is found (0 if not found)

So something like

If InStr(1, Range("C4").Value, "evening",1) > 0 Then
    Range("D5") = 5 + 2
ElseIf InStr(1, Range("C4").Value, "morning",1) > 0 Then
    Range("D5") = 5 + 1
End If

Hope that helps..

 

[MrKowz]

look at the Instr Function (works the same as find)

I really need to start remembering that evasive little VBA function. I have worksheetfunction.find littered throughout my modules.

 

[flicky_longcartridge]

I'll give those a try now, why would I use find instead of instr and vise versa, or will they both do the same sort of thing?

I intend to write something much more complex inside the if clauses, the 5 + 2 is just to see if it is working.

Thanks for all your help though

 

[MrKowz]

use InStr, it will work faster than the FIND I gave you.

 

[Jonmo1]

Generally speaking, if there's a VBA Native function that does the same thing as the worksheetfunction.xxx, use the Native VBA function.

Instr is a little more robust than the worksheet function find. It can be made to be case sensitive or NOT case sensitive. Find cannot, you would have to change it to search to be case sensitive..

 

[flicky_longcartridge]

As I have just found out!!,

The find method returned an error here, worked nicely if it was evening but not morning, whereas the instr works well for both.

Now I just have to develop the rest of the functionality!!

Thanks for all the swift responses!!