| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing negative number
Hi
I want to display a message based on the value of a variable but I can't seem to get the right 'greater than' and 'less than' signs correct for negative numbers It doesn't matter what the negative figure is it always goes to the last line ie. <(100) What am I doing wrong? Here is what I have so far: If bal 100 Then msg = "Drinks are on you tonight!" & vbCrLf & "But not too many!" ElseIf bal = 0 And bal < 100 Then msg = "Things are looking good!" msgstyle = vbOKOnly ElseIf bal < 0 And bal (50) Then msg = "Nearly there!" msgstyle = vbOKOnly + vbExclamation ElseIf bal < (50) And bal (100) Then msg = "No beer for you this week!" msgstyle = vbOKOnly + vbExclamation ElseIf bal < (100) Then msg = "Better ask mom and dad if you can stay for dinner!" msgstyle = vbOKOnly + vbCritical End If |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Comparing negative number
Hi
You may *format* a negative number as (50) in Excel (for -50), but the format is not recognized as a negative number by VBA. Here you have to use -50, -100 etc. What happens, if bal = -50 or bal = -100? Free softdrinks for the rest af the week :-) And what if bal = 100. I dare hardly ask :-) -- Best Regards Leo Heuser Followup to newsgroup only please. "Newbie" skrev i en meddelelse ... Hi I want to display a message based on the value of a variable but I can't seem to get the right 'greater than' and 'less than' signs correct for negative numbers It doesn't matter what the negative figure is it always goes to the last line ie. <(100) What am I doing wrong? Here is what I have so far: If bal 100 Then msg = "Drinks are on you tonight!" & vbCrLf & "But not too many!" ElseIf bal = 0 And bal < 100 Then msg = "Things are looking good!" msgstyle = vbOKOnly ElseIf bal < 0 And bal (50) Then msg = "Nearly there!" msgstyle = vbOKOnly + vbExclamation ElseIf bal < (50) And bal (100) Then msg = "No beer for you this week!" msgstyle = vbOKOnly + vbExclamation ElseIf bal < (100) Then msg = "Better ask mom and dad if you can stay for dinner!" msgstyle = vbOKOnly + vbCritical End If |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Comparing negative number
If isnumeric(bal) then
bal = cdbl(bal) If bal 100 Then msg = "Drinks are on you tonight!" & _ vbCrLf & "But not too many!" ElseIf bal = 0 Then msg = "Things are looking good!" msgstyle = vbOKOnly ElseIf bal -50 Then msg = "Nearly there!" msgstyle = vbOKOnly + vbExclamation ElseIf bal -100 Then msg = "No beer for you this week!" msgstyle = vbOKOnly + vbExclamation Else msg = "Better ask mom and dad if you can stay for dinner!" msgstyle = vbOKOnly + vbCritical End If Else msgbox "Bal isn't a number" End if Since your checks are progressive, there is no need to put in two conditions (50) is +50. Use a minus sign to make refer to a negative number. -- Regards, Tom Ogilvy "Newbie" wrote in message ... Hi I want to display a message based on the value of a variable but I can't seem to get the right 'greater than' and 'less than' signs correct for negative numbers It doesn't matter what the negative figure is it always goes to the last line ie. <(100) What am I doing wrong? Here is what I have so far: If bal 100 Then msg = "Drinks are on you tonight!" & vbCrLf & "But not too many!" ElseIf bal = 0 And bal < 100 Then msg = "Things are looking good!" msgstyle = vbOKOnly ElseIf bal < 0 And bal (50) Then msg = "Nearly there!" msgstyle = vbOKOnly + vbExclamation ElseIf bal < (50) And bal (100) Then msg = "No beer for you this week!" msgstyle = vbOKOnly + vbExclamation ElseIf bal < (100) Then msg = "Better ask mom and dad if you can stay for dinner!" msgstyle = vbOKOnly + vbCritical End If |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Comparing negative number
duh! I should have thought of that
Thanks for your help "Leo Heuser" wrote in message ... Hi You may *format* a negative number as (50) in Excel (for -50), but the format is not recognized as a negative number by VBA. Here you have to use -50, -100 etc. What happens, if bal = -50 or bal = -100? Free softdrinks for the rest af the week :-) And what if bal = 100. I dare hardly ask :-) -- Best Regards Leo Heuser Followup to newsgroup only please. "Newbie" skrev i en meddelelse ... Hi I want to display a message based on the value of a variable but I can't seem to get the right 'greater than' and 'less than' signs correct for negative numbers It doesn't matter what the negative figure is it always goes to the last line ie. <(100) What am I doing wrong? Here is what I have so far: If bal 100 Then msg = "Drinks are on you tonight!" & vbCrLf & "But not too many!" ElseIf bal = 0 And bal < 100 Then msg = "Things are looking good!" msgstyle = vbOKOnly ElseIf bal < 0 And bal (50) Then msg = "Nearly there!" msgstyle = vbOKOnly + vbExclamation ElseIf bal < (50) And bal (100) Then msg = "No beer for you this week!" msgstyle = vbOKOnly + vbExclamation ElseIf bal < (100) Then msg = "Better ask mom and dad if you can stay for dinner!" msgstyle = vbOKOnly + vbCritical End If |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Comparing negative number
Newbie,
Your tests are a bit screwed. For instance ElseIf bal < 0 And bal (50) Then will never pass as a variable cannot possibly be less than 0 and greater than 50, there is no such number. Similarly, ElseIf bal < (50) And bal (100) Then is an invalid test. Describe your different criteria in words, as I would juts be guessing what they are otherwise. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Newbie" wrote in message ... Hi I want to display a message based on the value of a variable but I can't seem to get the right 'greater than' and 'less than' signs correct for negative numbers It doesn't matter what the negative figure is it always goes to the last line ie. <(100) What am I doing wrong? Here is what I have so far: If bal 100 Then msg = "Drinks are on you tonight!" & vbCrLf & "But not too many!" ElseIf bal = 0 And bal < 100 Then msg = "Things are looking good!" msgstyle = vbOKOnly ElseIf bal < 0 And bal (50) Then msg = "Nearly there!" msgstyle = vbOKOnly + vbExclamation ElseIf bal < (50) And bal (100) Then msg = "No beer for you this week!" msgstyle = vbOKOnly + vbExclamation ElseIf bal < (100) Then msg = "Better ask mom and dad if you can stay for dinner!" msgstyle = vbOKOnly + vbCritical End If |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Comparing negative number
You're welcome.
LeoH "Newbie" skrev i en meddelelse ... duh! I should have thought of that Thanks for your help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I subtract a negative number from a positive number? | Excel Discussion (Misc queries) | |||
| Change positive number to negative number | Excel Worksheet Functions | |||
| Converting Negative Number to Postive number | Excel Worksheet Functions | |||
| Converting a negative number to a positive number | Excel Discussion (Misc queries) | |||
| 2003= negative number&2004= negative number How Do I Calculate gro | Excel Worksheet Functions | |||
Linear Mode
