If statement with upper and lower limit in vba not evaluating correctly

[lmoseley7]

I'm helping a coworker with a spreadsheet template. Users will enter a type of asset and depending upon the asset type they must enter a number of periods for depreciation that fits within a range. The asset types are entered as the result of a dropdown list so there should be no issues with misspellings or anything like that. In his VBA code there are two comparisons that deal with ranges and several that must equal a specific amount. One of the range comparions works and the other does not. The only difference is the numbers in the ranges, the code is exactly the same. Here is an excerpt of the code:

 If Range("C8") = "Land Improvements" Then
        If Range("C18") < "60" And Range("C18") > "240" Then Cancel = True
        End If
    If Range("C8") = "Leasehold Improvements" Then
        If Range("C18") < "12" And Range("C18") > "84" Then Cancel = True
        End If
End If
If Cancel = True Then MsgBox "ERROR: Useful life does not match policy."

[end code]

When C8 is "Land Improvements" numbers less than 60 and greater than 240 cause a message box to appear that tells the user they have entered a number outside of the range.  When C8 is "Leasehold Improvements" it won't return the message error regardless of the number entered into C18.

This is set up as a worksheet change macro, so I tried testing it as a normal macro that I could run and step into to try to see if I could tell where the validation was going wrong.  I made C18 a variable so that I could see what VBA was seeing in the cell in case there was some difference in what was coded in VBA and what was in the spreadsheet, but I saw no differences.  I also noted that if I break down the code to only validate either greater than  a value or less than a value for leasehold improvements, the code works.

Any ideas why the "If > and <" works for land improvements, but not leasehold improvements?  This is my first post so please be gentle.  I should also mention that I realize that data validation is another way to go with this, but the oddness of this issue begs a solution regardless of whether or not the issue is the best course of action.

Thanks,

 

[Jonmo1]

There is really only one sheet in the workbook with data and the code doesn't switch to the other sheet that contains the drop-down lists.

So it looks like that IS the problem...

Which sheet holds C18 (with the dropdown list)?
Which sheet module contains the code?

 

[lmoseley7]

Ok I give up. I tested the cell to see if the number was text by using the =istext() function and it returned false. I removed the "" from the numbers in the code and added the val function anyway. The code no longer worked with AND (as expected) but did work with OR (as it should). What I determined was that with OR the code worked with or without the "" around the numbers, but AND required the "" to be on the numbers or it would not work. So in a way, it seems that the "" were at least part of the issue, although that still doesn't explain why it worked for one and not the other. Since I am thoroughly convinced that the original code was wrong, I guess it doesn't matter why it seemed to be working. I just like things to be logical and this wasn't logical, so it was driving me crazy. Thanks so much for your help and patience. I know this must have seemed like a child repeatedly asking "But why?" when you've already given the answer.

 

[Jonmo1]

Don't sweat it.

The results of an illogical test were illogical...That's logical, like a double negative.

Just for curiosity's sake, do you still have a copy of the book that displayed this behavior (the And worked for one but not the other) ?
I'd be interested to see it, see if we can't figure out the why.
PM me if you wouldn't mind emailing that file to me.