| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
if statement conditions
In a file that I inherited there is the following code....
For Each cell In Range("RawDataList") fico = cell.Offset(0, 3) If fico "" And fico < "150" Then 'Bucket 1 PSICounts(0) = PSICounts(0) + 1 ElseIf fico = "150" And fico <= "199" Then 'Bucket 2 PSICounts(1) = PSICounts(1) + 1 ElseIf fico = "200" And fico <= "259" Then 'Bucket 3 PSICounts(2) = PSICounts(2) + 1 ElseIf fico = "260" Then 'Bucket 4 PSICounts(12) = PSICounts(12) + 1 Else 'Do nothing End If Next cell if fico is greater than 99 then it gets added to the correct bucket, but if fico is under 100 (it should go into bucket 1), it doesn't, but rather it goes into bucket 4. I realize that the if statements have the values in double quotes, but why would some fico numbers work and some don't. If I take out the double quotes in the if statement then it seems to work okay, which is understandable. I need to know why as soon as possilbe... also is there something that is being done wrong in the beginning if statement? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if statement conditions
When it does a text compare, it compares character by character, not the
whole number. So 7 is 260 because the 7 is greater than the 2. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Cheer-Phil-ly" wrote in message ... In a file that I inherited there is the following code.... For Each cell In Range("RawDataList") fico = cell.Offset(0, 3) If fico "" And fico < "150" Then 'Bucket 1 PSICounts(0) = PSICounts(0) + 1 ElseIf fico = "150" And fico <= "199" Then 'Bucket 2 PSICounts(1) = PSICounts(1) + 1 ElseIf fico = "200" And fico <= "259" Then 'Bucket 3 PSICounts(2) = PSICounts(2) + 1 ElseIf fico = "260" Then 'Bucket 4 PSICounts(12) = PSICounts(12) + 1 Else 'Do nothing End If Next cell if fico is greater than 99 then it gets added to the correct bucket, but if fico is under 100 (it should go into bucket 1), it doesn't, but rather it goes into bucket 4. I realize that the if statements have the values in double quotes, but why would some fico numbers work and some don't. If I take out the double quotes in the if statement then it seems to work okay, which is understandable. I need to know why as soon as possilbe... also is there something that is being done wrong in the beginning if statement? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if statement conditions
Maybe using numbers and not text would be better:
For Each cell In Range("RawDataList") fico = cell.Offset(0, 3) If fico 0 And fico < 150 Then 'Bucket 1 PSICounts(0) = PSICounts(0) + 1 ElseIf fico = 150 And fico <= 199 Then 'Bucket 2 PSICounts(1) = PSICounts(1) + 1 ElseIf fico = 200 And fico <= 259 Then 'Bucket 3 PSICounts(2) = PSICounts(2) + 1 ElseIf fico = 260 Then 'Bucket 4 PSICounts(12) = PSICounts(12) + 1 Else 'Do nothing End If or For Each cell In Range("RawDataList") fico = cell.Offset(0, 3) select case fico case is = 260 : PSICounts(12) = PSICounts(12) + 1 case is = 200 : PSICounts(2) = PSICounts(2) + 1 case is = 150 : PSICounts(1) = PSICounts(1) + 1 case is 0 : PSICounts(0) = PSICounts(0) + 1 case else 'do nothing end select next cell Cheer-Phil-ly wrote: In a file that I inherited there is the following code.... For Each cell In Range("RawDataList") fico = cell.Offset(0, 3) If fico "" And fico < "150" Then 'Bucket 1 PSICounts(0) = PSICounts(0) + 1 ElseIf fico = "150" And fico <= "199" Then 'Bucket 2 PSICounts(1) = PSICounts(1) + 1 ElseIf fico = "200" And fico <= "259" Then 'Bucket 3 PSICounts(2) = PSICounts(2) + 1 ElseIf fico = "260" Then 'Bucket 4 PSICounts(12) = PSICounts(12) + 1 Else 'Do nothing End If Next cell if fico is greater than 99 then it gets added to the correct bucket, but if fico is under 100 (it should go into bucket 1), it doesn't, but rather it goes into bucket 4. I realize that the if statements have the values in double quotes, but why would some fico numbers work and some don't. If I take out the double quotes in the if statement then it seems to work okay, which is understandable. I need to know why as soon as possilbe... also is there something that is being done wrong in the beginning if statement? -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if statement conditions
I'd take the quotes out, because you're mixing data types. You need
to be testing for a numeric, not a string which may mean you need to fico = val(cell.offset(0,3)) I'd also consider using SELECT CASE instead of the multi-level IF structure SELECT CASE fico Case 0 to 149 PSICounts(0) = PSICounts(0) + 1 Case 150 to 199 PSICounts(1) = PSICounts(1) + 1 Case 200 to 259 PSICounts(2) = PSICounts(2) + 1 Case " 260" PSICounts(12) = PSICounts(12) + 1 End Select |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if statement conditions
quote from OP ...
If I take out the double quotes in the if statement then it seems to work okay, which is understandable. I need to know why as soon as possible... -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave Peterson" wrote in message ... Maybe using numbers and not text would be better: For Each cell In Range("RawDataList") fico = cell.Offset(0, 3) If fico 0 And fico < 150 Then 'Bucket 1 PSICounts(0) = PSICounts(0) + 1 ElseIf fico = 150 And fico <= 199 Then 'Bucket 2 PSICounts(1) = PSICounts(1) + 1 ElseIf fico = 200 And fico <= 259 Then 'Bucket 3 PSICounts(2) = PSICounts(2) + 1 ElseIf fico = 260 Then 'Bucket 4 PSICounts(12) = PSICounts(12) + 1 Else 'Do nothing End If or For Each cell In Range("RawDataList") fico = cell.Offset(0, 3) select case fico case is = 260 : PSICounts(12) = PSICounts(12) + 1 case is = 200 : PSICounts(2) = PSICounts(2) + 1 case is = 150 : PSICounts(1) = PSICounts(1) + 1 case is 0 : PSICounts(0) = PSICounts(0) + 1 case else 'do nothing end select next cell Cheer-Phil-ly wrote: In a file that I inherited there is the following code.... For Each cell In Range("RawDataList") fico = cell.Offset(0, 3) If fico "" And fico < "150" Then 'Bucket 1 PSICounts(0) = PSICounts(0) + 1 ElseIf fico = "150" And fico <= "199" Then 'Bucket 2 PSICounts(1) = PSICounts(1) + 1 ElseIf fico = "200" And fico <= "259" Then 'Bucket 3 PSICounts(2) = PSICounts(2) + 1 ElseIf fico = "260" Then 'Bucket 4 PSICounts(12) = PSICounts(12) + 1 Else 'Do nothing End If Next cell if fico is greater than 99 then it gets added to the correct bucket, but if fico is under 100 (it should go into bucket 1), it doesn't, but rather it goes into bucket 4. I realize that the if statements have the values in double quotes, but why would some fico numbers work and some don't. If I take out the double quotes in the if statement then it seems to work okay, which is understandable. I need to know why as soon as possilbe... also is there something that is being done wrong in the beginning if statement? -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if statement conditions
"Maybe using numbers and not text would be better:"
(I could have been more explicit that comparing numbers with text is not always desired.) Bob Phillips wrote: quote from OP ... If I take out the double quotes in the if statement then it seems to work okay, which is understandable. I need to know why as soon as possible... -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave Peterson" wrote in message ... Maybe using numbers and not text would be better: For Each cell In Range("RawDataList") fico = cell.Offset(0, 3) If fico 0 And fico < 150 Then 'Bucket 1 PSICounts(0) = PSICounts(0) + 1 ElseIf fico = 150 And fico <= 199 Then 'Bucket 2 PSICounts(1) = PSICounts(1) + 1 ElseIf fico = 200 And fico <= 259 Then 'Bucket 3 PSICounts(2) = PSICounts(2) + 1 ElseIf fico = 260 Then 'Bucket 4 PSICounts(12) = PSICounts(12) + 1 Else 'Do nothing End If or For Each cell In Range("RawDataList") fico = cell.Offset(0, 3) select case fico case is = 260 : PSICounts(12) = PSICounts(12) + 1 case is = 200 : PSICounts(2) = PSICounts(2) + 1 case is = 150 : PSICounts(1) = PSICounts(1) + 1 case is 0 : PSICounts(0) = PSICounts(0) + 1 case else 'do nothing end select next cell Cheer-Phil-ly wrote: In a file that I inherited there is the following code.... For Each cell In Range("RawDataList") fico = cell.Offset(0, 3) If fico "" And fico < "150" Then 'Bucket 1 PSICounts(0) = PSICounts(0) + 1 ElseIf fico = "150" And fico <= "199" Then 'Bucket 2 PSICounts(1) = PSICounts(1) + 1 ElseIf fico = "200" And fico <= "259" Then 'Bucket 3 PSICounts(2) = PSICounts(2) + 1 ElseIf fico = "260" Then 'Bucket 4 PSICounts(12) = PSICounts(12) + 1 Else 'Do nothing End If Next cell if fico is greater than 99 then it gets added to the correct bucket, but if fico is under 100 (it should go into bucket 1), it doesn't, but rather it goes into bucket 4. I realize that the if statements have the values in double quotes, but why would some fico numbers work and some don't. If I take out the double quotes in the if statement then it seems to work okay, which is understandable. I need to know why as soon as possilbe... also is there something that is being done wrong in the beginning if statement? -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if statement conditions
Thanks Bob, Dave and Greg...
Bob your explanation of the text compare is enlightening... Guess I am going to have to find all code that is using the text compare and make the switch... also looks like using the Select Case is a better choice also. Thanks again! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If Statement On Two Conditions | Excel Worksheet Functions | |||
| Conditions with IF statement | Excel Discussion (Misc queries) | |||
| Two conditions in one IF statement | Excel Programming | |||
| why can i use 3 conditions in SQL statement only? | Excel Programming | |||
| 3 conditions if else statement. | Excel Programming | |||
Linear Mode
