Comparing negative number

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

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
 
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 :-)
 
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.
 
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)
 
Back
Top