If statement based on a find (search) in Excel...

[brans]

Can you do an If statement in Excel based on a Search finding certain criteria?

I want it to do one thing if A1 contains Branch and another thing if A1 contains Region...

Range("A1&quot.Select
Cells.Find(What:="branch", After:=Activecell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Activate
Cells.FindNext(After:=Activecell).Activate
ActiveCell.Offset(0, 50).Select
...

I want to be able to find branch and it run the correct code, and I want it to find region and it run different code (or the same code in a different order).

Is there any way to do this?

 

[dsi]

Is this what you are looking for?

Sub test()
    Dim sVal As String
    sVal = UCase(Range("A1").Text)
    If InStr(1, "BRANCH", sVal) > 0 Then
        'Do your Branch stuff
        MsgBox "Contains Branch"
    ElseIf InStr(1, "REGION", sVal) > 0 Then
        'Do your Region stuff
        MsgBox "Contains Region"
    End If
End Sub

 

[dsi]

Oops! That won't work. I have the strings reversed in the InStr function. Next time I should take the 5 seconds it takes to test it

Sub test()
    Dim sVal As String
    sVal = UCase(Range("A1").Text)
    If InStr(1, sVal, "BRANCH") > 0 Then
        'Do your Branch stuff
        MsgBox "Contains Branch"
    ElseIf InStr(1, sVal, "REGION") > 0 Then
        'Do your Region stuff
        MsgBox "Contains Region"
    End If
End Sub