Type Mismatch error when number is negative

[D.Farns]

Hello,

Need a little VBA help.

dim var1 as integer
var1 = countA - countB (for example)
If var1 >0 then

This if statement works fine when var1 is a possitive number. When var1 is
a negative number I get a "Type Mismatch" error. Should I not be able to
compare a negative value with another number? I've tried declaring 0 as
integer variable to ensure both variables being compared in the If statement
are of the same type, but that didn't help. Any suggestions?

 

[Jim Jackson]

Did you try "If var1 <>0 then"?

 

[D.Farns]

Jim,

To my surprise "var1 <> 0" did NOT produce the type mismatch error.
However, I want the if/then to evaluate to true only if var1 is 1 or higher.
var1 >=1 or var1 > 0 is the logic I need, but they both generate the error.

I tried this if/then earlier in the code to handle when var1 is negative.

if IsNumeric(var1) = false then
var1 = 0
end if

The hope was that when var1 is negative, the isnumeric would test false and
I could set it to 0 so I wouldn't get Type Mismatch in the next if/then, but
IsNumeric(var1) with var1 containing negative value evaluates to true.
Kind of strange that IsNumeric evaluates to true, but can't be compared to a
number?

any other thoughts?

 

[JLGWhiz]

It is sort of a bandaid type thing, but immediately after var1 value is
established, you could use an if statement:

If var1< 0 Then
var1 = 0
End If

Unless var1 value is used later in a calculation that would produce an
erroneous answer if converted to 0 value, it should cure your type mismatch
problem.

 

[NickHK]

Yes you can compare to negative number. But I suspect the type mismatch is
in the assignment of the value to var1;
- When countA - countB is positive a number is returned
- When countA - countB is negative, a non-integer value is returned

Private Sub CommandButton1_Click()
Dim var1 As Integer
var1 = -2
Debug.Print var1
If var1 > 0 Then
MsgBox "Positive"
Else
MsgBox "Negative"
End If

End Sub

NickHK

 

[NickHK]

For clarity:
Yes you can compare to negative number. But I suspect the type mismatch is
in the assignment of the value to var1;
OK - var1=<Some calculation that results in a +ve number>
Err - var1=<Some calculation that results in a non-integer value>

NickHK