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]

Welcome to the board..

Remove the Quote marks from the numbers..
"60" should just be 60

Also, the logic is flawed...
If Range("C18") < 60 And Range("C18") > 240
It's not possible for C18 to contain a number that is both < 60 AND > 240, there is no such number.

Do you mean OR?
If Range("C18") < 60 OR Range("C18") > 240

Or are you trying to say if C18 is BETWEEN 60 and 240 ?

Perhaps you have your < > symbols mixed up
< Means Less Than
> Means Greater Than

 

[lmoseley7]

Welcome to the board..

Remove the Quote marks from the numbers..
"60" should just be 60

Also, the logic is flawed...
If Range("C18") < 60 And Range("C18") > 240
It's not possible for C18 to contain a number that is both < 60 AND > 240, there is no such number.

Do you mean OR?
If Range("C18") < 60 OR Range("C18") > 240

Or are you trying to say if C18 is BETWEEN 60 and 240 ?

Perhaps you have your < > symbols mixed up
< Means Less Than
> Means Greater Than

I had some of the same thoughts when I first saw his code. We are looking for a number between the upper and lower numbers and if it isn't between them it would call the messae box to appear by changing Cancel to True. I've never had a reason to validate numbers like this using VBA so when I read the code I was thinking that a number would never fit that criteria, but I've seen other references through google searches where a between is written this way.

For the one that works, taking the "" off the numbers actually causes it not to validate correctly. I assume this is because the cell is formatted as text instead of a number value. I also thought that maybe if it is reading it as text then perhaps the > and < wouldn't work, but again, if you select Land Improvements, the code works fine.

 

[Jonmo1]

If you're looking to set Cancel = true if C18 is NOT BETWEEN 60 and 240, then

If Range("C18") < "60" And Range("C18") > "240" Then Cancel = True
should be
If Range("C18") < 60 OR Range("C18") > 240 Then Cancel = True


If C18 is a TEXT number, then you can put the Val function on it..
If Val(Range("C18")) < 60 OR Val(Range("C18")) > 240 Then Cancel = True

 

[lmoseley7]

Using OR instead of AND does seem to work for the selection of Leasehold Improvements where the range was 12 to 84. I'm not sure why the AND is working for the selection of Land Improvements where the range was 60 to 240. If I select Land Improvements and enter 50 or 250 into C18, I get the error message. Anything between 60 and 240 and I don't get the error message. I guess I'm just trying to figure out why the same code won't work for both situations?

I appreciate the help and the patience. Inconsistency in coding just bothers me because it doesn't seem logical. I'm sure there is a reason why it is working the way it is, but it is beyond me. I'll change both instances to use OR and let you know how it goes.

 

[lmoseley7]

Would there be any reason why a worksheet change event macro would work different from a macro in a module? Perhaps before the file was sent to me the signs were switched because I was unable to get the Land Improvements validations to work as I had seen before until I changed the AND to OR. I'll have to check with my coworker to confirm that his version had the opposite signs as mine, but in any event, using OR does seem to work now and makes more sense to me anyway. Thanks so much for your help.

 

[Jonmo1]

Yes, worksheet change event macros work differently from a macro in a module.
They are triggered automatically when cell(s) on the sheet change.

But, the meanings of > and < symbols are the same in either type of macro.

 

[lmoseley7]

Okay, I just confirmed that in the worksheet event macro the <> signs were in the same direction as in my module macro. OR works for both situations in the module macro but AND doesn't work for either (which is logical). In the worksheet change event macro AND works for Land Improvements only. I knew the difference in how the macros were triggered but I was wondering if there was a difference in syntax, although that wouldn't make sense to me. Have I found a bug or something?

 

[Jonmo1]

No it's not a bug...It's probably related to values being TEXT or something..

Post 2 codes.
1 that works
1 that doesn't


FYI, it doesn't matter if AND works for Land Improvement only. Because AND is wrong for this situation.
OR is the correct function for this case.

It could be that the Event Macro is referring to the wrong sheet.
Is your code switching between sheets by selecting them?

When you use Cells(....) in an Event code, it refers to the sheet that holds the code.
regardless of the sheet that's been selected/activated in previous lines of code.
If you want Cells(...) to refer to a sheet other than the sheet that holds the code, you must specify that sheet.
Sheets("SheetName").Cells(...)

 

[lmoseley7]

I agree that OR is the correct function for this case and there is a lot in this code that isn't spelled out. 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 I don't think switching between sheets is the issue. The text issue might be the culprit. I think I will add the val function to the code to see if it still works when evaluating it as numbers. In my original post, I gave two codes, one that worked and one that didn't, they were just written the same so it didn't make sense that it wouldn't work twice. That's why I posted this thread because I couldn't figure out why it wasn't consistent. The only differences in the code were the value being analyzed in C8 (Land Improvements vs. Leasehold Improvements) and the range of values allowed (60 to 240 vs. 12 to 84). The code is the same, the cells being used are the same, the sheet is the same; everything is the same except the outcome. I'll post back after testing the val function.