if statement not catching

V

vbaNewby

Board Regular
Joined
Jan 26, 2011
Messages
138
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:
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
vbaNewby,

Give this a try instead:
Code: 
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
 
Upvote 0
tigeravatar said:
vbaNewby,

Give this a try instead:
Code: 
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
Click to expand...
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.
 
Upvote 0
vbaNewby,

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

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


~tigeravatar
 
Upvote 0
tigeravatar said:
vbaNewby,

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

Code: 
If InStr(1, " " & ActiveCell.Value & " ", " depreciation ", vbTextCompare) > 0 _
Or InStr(1, " " & ActiveCell.Value & " ", " amortization ", vbTextCompare) > 0 Then
~tigeravatar
Click to expand...
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....?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Confusion about how call works within an if/elseif statement
Replies
2
Views
234
Moonbeam111
M
I
  • Question Question
Advice for slight alteration to existing working code
Replies
1
Views
232
ipbr21054
I
I
  • Solved
Hyperlink customer in another workbook to a file
2
Replies
11
Views
494
ipbr21054
I
Z
  • Solved
VBA: If…Then…Else Statements Issue
Replies
2
Views
418
zero269
Z
wizau
  • Solved
Nested IF statements, or a better way?
Replies
6
Views
409
wizau
wizau

Forum statistics

Threads
1,224,598
Messages
6,179,814
Members
452,945
Latest member
Bib195

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top