Results 1 to 3 of 3

Thread: New to VBA: Go to Next If Statement
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default New to VBA: Go to Next If Statement

    I have some code that I am messing with for a pet project at work for an inventory form. I have the pictures of an item with macros for a mouse click to remove one from the "on hand" inventory. in Sheet 1.

    From there, I have on Sheet 2 the "inventory database" which includes the Quantity on hand(which references the on hand qty in Sheet 1), the "trigger Qty" for the email to be sent, and the Target Qty. for reordering purposes and this is shown in my email.

    Where I am having trouble is: The program will run the FIRST line, but then when I adjust the Inventory for any other line item, it will NOT call mail for that line. As you can see I've tried to move all of my "Exit Sub" to the end of the data to attempt to let the macro run all of the above before exiting.

    Can someone please help?

    Thank you!

    Code:
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    'SAJ
        
        If Worksheets("TAG_INV").Range("F2") < 0 Then
                Call Mail_SAJ
    'SB2
    
    
        If Worksheets("TAG_INV").Range("F3") < 0 Then
                Call Mail_SB2
    'S43_SAN
        
        If Worksheets("TAG_INV").Range("F9") < 0 Then
                Call Mail_S43_SAN
    'S00_S17
        
        If Worksheets("TAG_INV").Range("F8") < 0 Then
                Call Mail_S00_S17
    'SAH_SCE
        
        If Worksheets("TAG_INV").Range("F4") < 0 Then
                Call Mail_SAH_SCE
    'SAF_SCU
        
        If Worksheets("TAG_INV").Range("F6") < 0 Then
                Call Mail_SAF_SCU
    'SB5_SCN
        
        If Worksheets("TAG_INV").Range("F7") < 0 Then
                Call Mail_SB5_SCN
    'SB9_SCF
        
        If Worksheets("TAG_INV").Range("F5") < 0 Then
                Call Mail_SB9_SCF
    'SC8
        
        If Worksheets("TAG_INV").Range("F11") < 0 Then
                Call Mail_SC8
    'SBK
        
        If Worksheets("TAG_INV").Range("F10") < 0 Then
                Call Mail_SBK
    'SB2
        
        If Worksheets("TAG_INV").Range("F3    ") < 0 Then
                Call Mail_SB2
        
        If Worksheets("TAG_INV").Range("D2") > Worksheets("TAG_INV").Range("C2") Then Exit Sub
        If Worksheets("TAG_INV").Range("D3") > Worksheets("TAG_INV").Range("C3") Then Exit Sub
        If Worksheets("TAG_INV").Range("D9") > Worksheets("TAG_INV").Range("C9") Then Exit Sub
        If Worksheets("TAG_INV").Range("D8") > Worksheets("TAG_INV").Range("C8") Then Exit Sub
        If Worksheets("TAG_INV").Range("D4") > Worksheets("TAG_INV").Range("C4") Then Exit Sub
        If Worksheets("TAG_INV").Range("D6") > Worksheets("TAG_INV").Range("C6") Then Exit Sub
        If Worksheets("TAG_INV").Range("D7") > Worksheets("TAG_INV").Range("C7") Then Exit Sub
        If Worksheets("TAG_INV").Range("D5") > Worksheets("TAG_INV").Range("C5") Then Exit Sub
        If Worksheets("TAG_INV").Range("D11") > Worksheets("TAG_INV").Range("C11") Then Exit Sub
        If Worksheets("TAG_INV").Range("D10") > Worksheets("TAG_INV").Range("C10") Then Exit Sub
        If Worksheets("TAG_INV").Range("D3") > Worksheets("TAG_INV").Range("C3") Then Exit Sub
        
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
        End If
         
        
        End If
        
    End Sub

  2. #2
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,298
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: New to VBA: Go to Next If Statement

    Think you need to try using a SELECT CASE statement, try this link for details: https://www.excel-easy.com/vba/examp...lect-case.html


  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,331
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: New to VBA: Go to Next If Statement

    How about

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Worksheets("TAG_INV").Range("F2") < 0 Then Call Mail_SAJ
        If Worksheets("TAG_INV").Range("F3") < 0 Then Call Mail_SB2
        If Worksheets("TAG_INV").Range("F9") < 0 Then Call Mail_S43_SAN
        If Worksheets("TAG_INV").Range("F8") < 0 Then Call Mail_S00_S17
        If Worksheets("TAG_INV").Range("F4") < 0 Then Call Mail_SAH_SCE
        If Worksheets("TAG_INV").Range("F6") < 0 Then Call Mail_SAF_SCU
        If Worksheets("TAG_INV").Range("F7") < 0 Then Call Mail_SB5_SCN
        If Worksheets("TAG_INV").Range("F5") < 0 Then Call Mail_SB9_SCF
        If Worksheets("TAG_INV").Range("F11") < 0 Then Call Mail_SC8
        If Worksheets("TAG_INV").Range("F10") < 0 Then Call Mail_SBK
        
    'I do not understand this part of your code, since it does not do anything.
        If Worksheets("TAG_INV").Range("D2") > Worksheets("TAG_INV").Range("C2") Then Exit Sub
        If Worksheets("TAG_INV").Range("D3") > Worksheets("TAG_INV").Range("C3") Then Exit Sub
        If Worksheets("TAG_INV").Range("D9") > Worksheets("TAG_INV").Range("C9") Then Exit Sub
        If Worksheets("TAG_INV").Range("D8") > Worksheets("TAG_INV").Range("C8") Then Exit Sub
        If Worksheets("TAG_INV").Range("D4") > Worksheets("TAG_INV").Range("C4") Then Exit Sub
        If Worksheets("TAG_INV").Range("D6") > Worksheets("TAG_INV").Range("C6") Then Exit Sub
        If Worksheets("TAG_INV").Range("D7") > Worksheets("TAG_INV").Range("C7") Then Exit Sub
        If Worksheets("TAG_INV").Range("D5") > Worksheets("TAG_INV").Range("C5") Then Exit Sub
        If Worksheets("TAG_INV").Range("D11") > Worksheets("TAG_INV").Range("C11") Then Exit Sub
        If Worksheets("TAG_INV").Range("D10") > Worksheets("TAG_INV").Range("C10") Then Exit Sub
        If Worksheets("TAG_INV").Range("D3") > Worksheets("TAG_INV").Range("C3") Then Exit Sub
    End Sub
    Regards Dante Amor

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
  •