if statement not catching

[vbaNewby]

Hello guru's. I have an if statement within a select case statement and the code for some reason or another is unreachable. No compiler errors but its not jumping into the "if" statement.

Here's the code:

Select Case ActiveCell.Offset(0, 1).Value
 Case "Expense"
                MsgBox "Found Expense" 'gets here
                a = Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & "DEPRECIATION" & " ", " " & UCase(ActiveCell) & " "))
                b = Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & "AMORTIZATION" & " ", " " & UCase(ActiveCell.Value) & " "))
If a = "True" Or b = "True" Then 'doesn't get here
                        'do something

   Else
                         MsgBox "in else statement"  'doesn't get here
                      'do stuff
                    End If
end select

Any ideas?

 

[tigeravatar]

vbaNewby,

Give this a try instead:

Sub tgr()
    
    Select Case LCase(Trim(ActiveCell.Offset(0, 1).Value))
        Case "expense"
            MsgBox "Found Expense" 'gets here
            If InStr(1, ActiveCell.Value, "depreciation", vbTextCompare) > 0 _
            Or InStr(1, ActiveCell.Value, "amortization", vbTextCompare) > 0 Then
                MsgBox "found ""depreciation"" or ""amortization"""
            Else
                MsgBox "in else statement"
            End If
    End Select
    
End Sub

Hope that helps,
~tigeravatar

 

[vbaNewby]

vbaNewby,

Give this a try instead:

Sub tgr()
    
    Select Case LCase(Trim(ActiveCell.Offset(0, 1).Value))
        Case "expense"
            MsgBox "Found Expense" 'gets here
            If InStr(1, ActiveCell.Value, "depreciation", vbTextCompare) > 0 _
            Or InStr(1, ActiveCell.Value, "amortization", vbTextCompare) > 0 Then
                MsgBox "found ""depreciation"" or ""amortization"""
            Else
                MsgBox "in else statement"
            End If
    End Select
    
End Sub

Hope that helps,
~tigeravatar

Thanks tiger but I need to use excel formula for my string finds. What it does is handles spaces the way I want it to.

For example if ActiveCell.Value = "xdepreciationx", the instr function will return true. I do not want this.

 

[tigeravatar]

vbaNewby,

You can put the spaces in the instr method, i just didn't know you explicitly needed them:

If InStr(1, " " & ActiveCell.Value & " ", " depreciation ", vbTextCompare) > 0 _
Or InStr(1, " " & ActiveCell.Value & " ", " amortization ", vbTextCompare) > 0 Then

~tigeravatar

 

[vbaNewby]

vbaNewby,

You can put the spaces in the instr method, i just didn't know you explicitly needed them:

If InStr(1, " " & ActiveCell.Value & " ", " depreciation ", vbTextCompare) > 0 _
Or InStr(1, " " & ActiveCell.Value & " ", " amortization ", vbTextCompare) > 0 Then

~tigeravatar

Thanks Tiger, I will give that a shot. Happen to know why the if statement doesn't catch? I ask because I have to modify a ton of code and would rather keep the same structure....?