2 If/Then statements in 1 sub not working correctly

J

jeff

I have messed around with this for a couple days. This should be so
simple, but I can’t get it right.

When user hits the OK button, I want VBA to check the entry of
TextBox2 VS the range value of Base_Pay.
If the TextBox2 value is less, then execute 1 routine. If it’s more,
then a different routine. It won’t seem to recognize if its less, so
nothing happens. No error.

If I take out the top part (less than), and just leave in the If More
part, then it works fine. Why can’t I get the top section for If Less
to work? Is there something about the less than < sign VBA doesn’t
like? Do I need to make Declarations?
Note: TextBox1 shows current date.
Thanks for your help.
j.o.

Private Sub CommandButton2_Click()
'OK button

‘1st section checks to see if TextBox2 is less than
range Base_Pay

‘ ***I’ve tried this statement both ways, neither works***
'If Range("Base_Pay").Value > (TextBox2.Value) Then
If (TextBox2.Value) < Range("Base_Pay").Value Then

Application.Goto Reference:="Account_1"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = TextBox1.Value
ActiveCell.Offset(0, 3).Select ‘Goes to Debt column
'ActiveCell.Value = Range("Base_Pay").Value - TextBox2.Value

Else ‘ I’ve tried this with and without Else part

' ***this section works fine if the top section is disabled***
If (TextBox2.Value) > Range("Base_Pay").Value Then
Application.Goto Reference:="Account_1"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = TextBox1.Value
ActiveCell.Offset(0, 4).Select ‘Goes to Credit column
ActiveCell.Value = Range("Base_Pay").Value - TextBox2.Value

End If
End If

‘AllocatePay ‘ this is where it goes after performing routine above.
This works fine. I disabled this during testing.


End Sub
 
J

Jim Cone

A textbox returns text not numbers. Try CDbl(TextBox2.Value)
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)




"jeff" <[email protected]>
wrote in message
I have messed around with this for a couple days. This should be so
simple, but I can’t get it right.

When user hits the OK button, I want VBA to check the entry of
TextBox2 VS the range value of Base_Pay.
If the TextBox2 value is less, then execute 1 routine. If it’s more,
then a different routine. It won’t seem to recognize if its less, so
nothing happens. No error.

If I take out the top part (less than), and just leave in the If More
part, then it works fine. Why can’t I get the top section for If Less
to work? Is there something about the less than < sign VBA doesn’t
like? Do I need to make Declarations?
Note: TextBox1 shows current date.
Thanks for your help.
j.o.

Private Sub CommandButton2_Click()
'OK button

‘1st section checks to see if TextBox2 is less than range Base_Pay

‘ ***I’ve tried this statement both ways, neither works***
'If Range("Base_Pay").Value > (TextBox2.Value) Then
If (TextBox2.Value) < Range("Base_Pay").Value Then

Application.Goto Reference:="Account_1"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = TextBox1.Value
ActiveCell.Offset(0, 3).Select ‘Goes to Debt column
'ActiveCell.Value = Range("Base_Pay").Value - TextBox2.Value

Else ‘ I’ve tried this with and without Else part

' ***this section works fine if the top section is disabled***
If (TextBox2.Value) > Range("Base_Pay").Value Then
Application.Goto Reference:="Account_1"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = TextBox1.Value
ActiveCell.Offset(0, 4).Select ‘Goes to Credit column
ActiveCell.Value = Range("Base_Pay").Value - TextBox2.Value

End If
End If

‘AllocatePay ‘ this is where it goes after performing routine above.
This works fine. I disabled this during testing.
End Sub
 
J

jeff

A textbox returns text not numbers.  Try CDbl(TextBox2.Value)
--
Jim Cone
Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)

"jeff" <[email protected]>
wrote in messageI have messed around with this for a couple days. This should be so
simple, but I can t get it right.

When user hits the OK button, I want VBA to check the entry of
TextBox2 VS the range value of Base_Pay.
If  the TextBox2 value is less, then execute 1 routine. If it s more,
then a different routine. It won t seem to recognize if its less, so
nothing happens. No error.

If I take out the top part (less than), and just leave in the If More
part, then it works fine. Why can t I get the top section for If Less
to work? Is there something about the less than < sign VBA doesn t
like? Do I need to make Declarations?
Note: TextBox1 shows current date.
Thanks for your help.
j.o.

Private Sub CommandButton2_Click()
'OK button

1st section checks to see if TextBox2 is less than range Base_Pay

   ***I ve tried this statement both ways, neither works***
'If Range("Base_Pay").Value > (TextBox2.Value) Then
If (TextBox2.Value) < Range("Base_Pay").Value Then

Application.Goto Reference:="Account_1"
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = TextBox1.Value
ActiveCell.Offset(0, 3).Select   Goes to Debt column
'ActiveCell.Value = Range("Base_Pay").Value - TextBox2.Value

Else   I ve tried this with and without Else part

  ' ***this section works fine if the top section is disabled***
If (TextBox2.Value) > Range("Base_Pay").Value Then
Application.Goto Reference:="Account_1"
   Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
  ActiveCell.Value = TextBox1.Value
  ActiveCell.Offset(0, 4).Select   Goes to Credit column
ActiveCell.Value = Range("Base_Pay").Value - TextBox2.Value

End If
End If

AllocatePay   this is where it goes after performing routine above.
This works fine. I disabled this during testing.
End Sub

I'm more a novice than expert here. But, I've used numbers in
Textboxes for calculations all the time. In fact, the If statement in
the bottom section calculates just fine if the top section isn't
there. I can't get the top If statement section to be recognized.
I appreciate your reply.
 
M

myford100

I'm with Jim Cone in this one. The reason your IF statement doesn't
work is the numerical value of the text in the box is zero, rather
than the dollar value. Did you try Jim Cone's suggestion, just so you
could say you did?

Doing arithmetics on a raw value in a textbox is one of those times
when VB/VBA tries to guess what you meant and accommodate you.
However, with the IF statement, it guesses that you probably meant the
text in the box to be text.

Some problems of this type can be prevented if you set Option Strict
on and Option Infer Off--though I'd probably make one that would
still come crashing through, especially if people were watching..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top