Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: If Cell Value Greater Than -1 Exit Sub

  1. #1
    Board Regular
    Join Date
    Nov 2015
    Posts
    344
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default If Cell Value Greater Than -1 Exit Sub

    Hi can anyone help with vba code?

    I have a Sub named 'My Data'

    I have values in cells D3 and D4 Sheet2 and want to call a Sub named 'DataValues' then exit sub but only when D3 = D4 greater than -1

    eg

    (A) - if D3 value is 1 and D4 value is 3 then I want Sub 'My Data' to call Sub 'DataValues' then exit Sub.

    but

    (B) - if D3 value is 1 and D4 value is 2 then I just want to end the IF statement and continue with the Sub 'My Data'

    (C)- if D3 value is 7 and D4 value is 5 then I just want to end the IF statement and continue with the Sub 'My Data'

    The code I have works with the line highlighted in red when (A) is true but still calls Sub 'DataValues' when (B) and (C) is true.

    Code:
    Sub MyData()
    
    
        Dim FromSht As Worksheet: Set FromSht = Sheet1
        Dim ToSht As Worksheet: Set ToSht = Sheet2
        Dim lRowW As Long
    
        
        lRowW = FromSht.Cells(Rows.Count, "W").End(xlUp).Row
            
            With ToSht
                    If Range("D4") = "" Then
                        GoTo Continue
                    End If
                If Range("D3") = Range("D4") > -1 Then
                    Call DataValues
                    Exit Sub
                End If
            End With
        
    Continue:
            If lRowW = 2 Then
                FromSht.Range("W2").ClearContents
                        Range("D4").ClearContents
                        Exit Sub
                    Else:
                        If lRowW = 1 Then
                        Range("D3").ClearContents
                    Exit Sub
                End If
            End If
    End Sub

    Any help would be apprieciated

    Regards

    pwill

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,412
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: If Cell Value Greater Than -1 Exit Sub

    If Range("D3") = Range("D4") and Range("D4") > -1 Then.....

  3. #3
    Board Regular
    Join Date
    Nov 2015
    Posts
    344
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Cell Value Greater Than -1 Exit Sub

    Thanks oldbrewer, I will give that a try

    much appreciated

    Regards

    pwill

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    12,144
    Post Thanks / Like
    Mentioned
    225 Post(s)
    Tagged
    15 Thread(s)

    Default Re: If Cell Value Greater Than -1 Exit Sub

    Maybe
    Code:
    If Range("D4").Value > Range("D3").Value + 1 Then
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  5. #5
    Board Regular
    Join Date
    Nov 2015
    Posts
    344
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Cell Value Greater Than -1 Exit Sub

    Thanks Fluff,

    Both solutions work when (A) is true in my example but still continues to call DataValues when (B) and (C) are true?

    really struggling with this one think its got me beat?

    pwill

  6. #6
    Board Regular
    Join Date
    Nov 2015
    Posts
    344
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Cell Value Greater Than -1 Exit Sub

    Just to recap, I don't want to call DataValues if (B) and (C) are true

    regards

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    12,144
    Post Thanks / Like
    Mentioned
    225 Post(s)
    Tagged
    15 Thread(s)

    Default Re: If Cell Value Greater Than -1 Exit Sub

    Which sheet are those ranges on? At the moment your code is looking at the active sheet.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  8. #8
    Board Regular
    Join Date
    Nov 2015
    Posts
    344
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Cell Value Greater Than -1 Exit Sub

    Hi, they are on sheet2

  9. #9
    Board Regular
    Join Date
    Nov 2015
    Posts
    344
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Cell Value Greater Than -1 Exit Sub

    Hi, I'm wondering if I have the terminology correct in the title? Maybe it should be 'If Cell Value less Than -1 Exit Sub'

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    12,144
    Post Thanks / Like
    Mentioned
    225 Post(s)
    Tagged
    15 Thread(s)

    Default Re: If Cell Value Greater Than -1 Exit Sub

    In that case try
    Code:
    Sub MyData()
       
       
       Dim FromSht As Worksheet: Set FromSht = Sheet10
       Dim ToSht As Worksheet: Set ToSht = Sheet2
       Dim lRowW As Long
       
       
       lRowW = FromSht.Cells(Rows.Count, "A").End(xlUp).Row
       
       With ToSht
          If .Range("D4") = "" Then
             GoTo Continue
          End If
          If .Range("D4").Value > .Range("D3").Value + 1 Then
             Call DataValues
             Exit Sub
          End If
       End With
       
    Continue:
       If lRowW = 2 Then
          FromSht.Range("W2").ClearContents
          ToSht.Range("D4").ClearContents
          Exit Sub
       Else:
          If lRowW = 1 Then
             ToSht.Range("D3").ClearContents
             Exit Sub
          End If
       End If
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

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
  •