Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

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

  1. #1
    New Member vicsar's Avatar
    Join Date
    May 2013
    Location
    Heredia, Costa Rica
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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/7r4byo0wud...ults.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:

    Code:
    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

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    18,928
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    I think you mean <= -1
    -2 is less than -1
    -3 is less than -1
    etc.
    Office 2010/2016

  3. #3
    Board Regular
    Join Date
    May 2014
    Posts
    1,590
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Code:
    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
        ' Dont know how this works but moving on...
        Set rngT5 = Cells.Find("Check (Should be Zero) (E-F)")
    
        For Each myCel in rngT5
        ' If found then...
        If Not rngT5 Is Nothing Then 'delete this
        If myCel <> Empty Then
            Cells(rngT5.row, rngT5.Column + 3).Activate 'delete this
             dblFrm7 = Cells(rngT5.row, rngT5.Column + 3)
            dblFrm7 = ActiveCell.value 'delete this
                            
            #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
        Next myCel
    End Sub
    Can it be done with Excel? Is it possible? Yes and Yes.

  4. #4
    New Member vicsar's Avatar
    Join Date
    May 2013
    Location
    Heredia, Costa Rica
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Scott Huish View Post
    I think you mean <= -1
    -2 is less than -1
    -3 is less than -1
    etc.
    ...my bad, you are right.

  5. #5
    New Member vicsar's Avatar
    Join Date
    May 2013
    Location
    Heredia, Costa Rica
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by WarPigl3t View Post
    Code:
    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
        ' Dont know how this works but moving on...
        Set rngT5 = Cells.Find("Check (Should be Zero) (E-F)")
    
        For Each myCel in rngT5
        ' If found then...
        If Not rngT5 Is Nothing Then 'delete this
        If myCel <> Empty Then
            Cells(rngT5.row, rngT5.Column + 3).Activate 'delete this
             dblFrm7 = Cells(rngT5.row, rngT5.Column + 3)
            dblFrm7 = ActiveCell.value 'delete this
                            
            #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
        Next myCel
    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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com