VBA Excel Thinks Number is Lower Than Another

[realniceguy5000]

Hi, I have this script below:For some reason excel things the upcstring is lower then LowerTH string?

Setup is:

Range("A17").Value is 15.5 Cell Format is Text
Range("K35") is a formula that results in 14.00
LowerTH is Range("35").Value - 5 results are 9.00

When the script reaches the if statment below it should by pass the inputbox because the upcstring is a higher number than the lowerTH number, But instead it goes right to the inputbox which means excel thinks that the upcstring is lower than the LowerTH number?

If upcstring < LowerTH Then

Maybe I need to change the text string back to a number?

Not sure???

Thank You, Mike

Here is the script...

Sub Weight()
Stop
 
    If Sheets(2).Range("A1").Value = "Please Enter Weight From Scale" Then
 
        upcstring = Sheets(2).Range("A17").Value
            mycount = Len(upcstring)
        If mycount = "" Or mycount > 5 Then
            MsgBox "Please Enter Weight Again", vbOKOnly, "Data Error"
                Sheets(2).Range("A17").Value = ""
 
        ElseIf Sheets(1).Range("K35").Value > 0 Then
        Stop
 
 
       [COLOR=red]upcstring = Format(upcstring, "0.00")[/COLOR]
[COLOR=red]           myavg = Sheets(1).Range("K35").Value[/COLOR]
[COLOR=red]               myavg = Format(myavg, "0.00")[/COLOR]
[COLOR=red]                   UpperTH = myavg + 5#: UpperTH = Format(UpperTH, "0.00")[/COLOR]
[COLOR=red]                       LowerTH = myavg - 5#: LowerTH = Format(LowerTH, "0.00")[/COLOR]
[COLOR=red]                           'If upcstring '> UpperTH  'Or[/COLOR]
[COLOR=red]                           If upcstring < LowerTH Then[/COLOR]
 
                                NewValue = Application.InputBox("Is This Weight Correct? " & upcstring & " lbs", "Weight Check", upcstring)
                            End If
 
         Stop
 
 
 
        Else
            With Sheets(1)
            lrow = .Cells(21, 5).End(xlUp).Row + 1
            .Cells(lrow, 5).Value = upcstring
            End With
 
            With Sheets(2)
             .Range("A1").Value = "Checking Data..."
            .Range("A1").Interior.ColorIndex = 50
            .Range("A17").Select
            .Range("A17").Value = ""
 
            End With
        End If
    End If
 
End Sub

 

[Richard Schollar]

Convert both to a number in the code and run the test:

If Val(upcstring) < Val(LowerTH) Then

If it works, you know it is a text comparison issue.

 

[RoryA]

Yep. As a text comparison, 15 is less than 9 as it starts with 1. It would be helpful if you declared some variables and just used number types to compare numbers...

 

[realniceguy5000]

Working Now, Thanks for the help guys.I should have declared those variables...

Thank You, Mike