VBA greather than 0 or greater than -1 fails to produce desired results.

[vicsar]

Hello and good day.

Please help me understand what I am doing wrong. Why is this failing? How can I fix it? I have tried so many ways and yet it fails... it shouldn't be so difficult.

Example workbook:
* I am not allowed to post attachments in the forum yet.
https://www.dropbox.com/s/7r4byo0wu...-1-fails-to-produce-desired-results.xlsm?dl=0

Goal:
To (for the sake of the example) show a dialog box only if the value in certain cell is greater than zero or greater than -1.
Thus:

• If the returned value is 1 (1, 2, 3, and etcetera) a warning is displayed.
• If the value returned is greater than or equal to -1 (-1, -2, -3, and etcetera) a warning is displayed.
• No warning should be displayed if the value in the cell is between 0 and -1. E.g.: 0.01, 0.0000000000000000009, or 0.999999999999999999999999 should be ignored.

Here is the latest and simplest approach I've tried to find a solution:

Sub Check_Point()
    '8 Check (Should be Zero) (E-F) field must be zero
    Dim rngT5 As Range
    Dim dblFrm7 As Double
    
    ' Searching for a unique cell contaning a knomn string to use it as reference
    Set rngT5 = Cells.Find("Check (Should be Zero) (E-F)")

    ' If found then...
    If Not rngT5 Is Nothing Then
        Cells(rngT5.row, rngT5.Column + 3).Activate
        dblFrm7 = ActiveCell.value
                        
        #If Debugging Then
        Debug.Print "dblFrm7: " & dblFrm7
        #End If

        If dblFrm7 > 0 Then
                        
            MsgBox "Value is greater than 0", vbOKOnly Or vbExclamation, "Warning"
            
        ElseIf dblFrm7 >= -1 Then

            MsgBox "Value is greater than or equal to -1", vbOKOnly Or vbExclamation, "Warning"

        End If
     
    End If

End Sub

 

[Scott Huish]

I think you mean <= -1
-2 is less than -1
-3 is less than -1
etc.

 

[WarPigl3t]

Sub Check_Point()
    '8 Check (Should be Zero) (E-F) field must be zero
    Dim rngT5 As Range
    Dim dblFrm7 As Double
    
    ' Searching for a unique cell contaning a knomn string to use it as reference
 [COLOR=#008000]   ' Dont know how this works but moving on...[/COLOR]
    Set rngT5 = Cells.Find("Check (Should be Zero) (E-F)")

    [B]For Each myCel in rngT5[/B]
    ' If found then...
    If Not rngT5 Is Nothing Then[COLOR=#ff0000] 'delete this[/COLOR]
    [B]If myCel <> Empty Then[/B]
        Cells(rngT5.row, rngT5.Column + 3).Activate [COLOR=#ff0000]'delete this[/COLOR]
         [B]dblFrm7 = Cells(rngT5.row, rngT5.Column + 3)[/B]
        dblFrm7 = ActiveCell.value[COLOR=#ff0000] 'delete this[/COLOR]
                        
        #If Debugging Then
        Debug.Print "dblFrm7: " & dblFrm7
        #End If

        If dblFrm7 > 0 Then
            
            MsgBox "Value is greater than 0", vbOKOnly Or vbExclamation, "Warning"
         
        ElseIf dblFrm7 >= -1 Then

            MsgBox "Value is greater than or equal to -1", vbOKOnly Or vbExclamation, "Warning"

        End If
     
    End If
[B]    Next myCel[/B]
End Sub

 

[vicsar]

I think you mean <= -1
-2 is less than -1
-3 is less than -1
etc.

...my bad, you are right.

 

[vicsar]

Sub Check_Point()
    '8 Check (Should be Zero) (E-F) field must be zero
    Dim rngT5 As Range
    Dim dblFrm7 As Double
    
    ' Searching for a unique cell contaning a knomn string to use it as reference
 [COLOR=#008000]   ' Dont know how this works but moving on...[/COLOR]
    Set rngT5 = Cells.Find("Check (Should be Zero) (E-F)")

    [B]For Each myCel in rngT5[/B]
    ' If found then...
    If Not rngT5 Is Nothing Then[COLOR=#ff0000] 'delete this[/COLOR]
    [B]If myCel <> Empty Then[/B]
        Cells(rngT5.row, rngT5.Column + 3).Activate [COLOR=#ff0000]'delete this[/COLOR]
         [B]dblFrm7 = Cells(rngT5.row, rngT5.Column + 3)[/B]
        dblFrm7 = ActiveCell.value[COLOR=#ff0000] 'delete this[/COLOR]
                        
        #If Debugging Then
        Debug.Print "dblFrm7: " & dblFrm7
        #End If

        If dblFrm7 > 0 Then
            
            MsgBox "Value is greater than 0", vbOKOnly Or vbExclamation, "Warning"
         
        ElseIf dblFrm7 >= -1 Then

            MsgBox "Value is greater than or equal to -1", vbOKOnly Or vbExclamation, "Warning"

        End If
     
    End If
[B]    Next myCel[/B]
End Sub

Thank you for taking the time, the problem persists yet we were told we no longer need this check in the tool. So we are sort of off the hook for now.