Results 1 to 9 of 9

Thread: Help with simple routine
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2008
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with simple routine

    I will never figure this stull out! Must be brain dead....Keep getting Else without If line 3

    Code:
    If Delete_Lst = Empty Then
        End If
    ElseIf Delete_Lst = vbNo Then
        Exit Sub
    ElseIf Delete_Lst = vbYes Then
        Delete_List
    Else
    '
    End If
    Thanks in advance!

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,244
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Help with simple routine

    Quote Originally Posted by chobe View Post
    I will never figure this stull out! Must be brain dead....Keep getting Else without If line 3

    Code:
    If Delete_Lst = Empty Then
        End If
    ElseIf Delete_Lst = vbNo Then
        Exit Sub
    ElseIf Delete_Lst = vbYes Then
        Delete_List
    Else
    '
    End If
    Thanks in advance!
    The highlighted "End If" is closing off the "If..Then" above it so your "End If" at the end does not have an "If..Then" to pair with. Omit it and the "If..Then" will end automatically as there is no code in that block to execute.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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

    Default Re: Help with simple routine

    In this case you can use Select Case statement, I consider it easier to use.

    Code:
        Select Case Delete_Lst
            Case Empty
                'Action a
            Case vbNo
                Exit Sub
            Case vbYes
                Delete_List
            Case Else
                'Action b
        End Select
    Regards Dante Amor

  4. #4
    New Member
    Join Date
    Oct 2008
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with simple routine

    Quote Originally Posted by Rick Rothstein View Post
    The highlighted "End If" is closing off the "If..Then" above it so your "End If" at the end does not have an "If..Then" to pair with. Omit it and the "If..Then" will end automatically as there is no code in that block to execute.
    Thanks Rick! Kinda figured when same End If was last test in the loop. Then Excel gave a clear error "End If without Block If".

    Question is then, how do you exit a if then loop without exiting the entire sub?

  5. #5
    New Member
    Join Date
    Oct 2008
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with simple routine

    Thanks Dante = will give it a try

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,244
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Help with simple routine

    Quote Originally Posted by chobe View Post
    Thanks Rick! Kinda figured when same End If was last test in the loop. Then Excel gave a clear error "End If without Block If".

    Question is then, how do you exit a if then loop without exiting the entire sub?
    If you structure your If..Then correctly, only the items you want to be tested will be tested and anything else will just fall through to the statement after the End If. For example,
    Code:
    If X = 25 Then
      MsgBox "X equals 25"
    ElseIf X = 30 Then
      MsgBox "X equals 30"
    End If
    In the above code, X = 25 or X = 30 is the only thing the IF..Then block will react to... for any other value, the If..Then block simply ignores it and execution resumes at the statement after the End If.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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

    Default Re: Help with simple routine

    Quote Originally Posted by chobe View Post
    Thanks Dante = will give it a try
    Youre welcome, let me know if you have any questions.
    Regards Dante Amor

  8. #8
    New Member
    Join Date
    Oct 2008
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Help with simple routine

    Quote Originally Posted by Rick Rothstein View Post
    If you structure your If..Then correctly, only the items you want to be tested will be tested and anything else will just fall through to the statement after the End If. For example,
    Code:
    If X = 25 Then
      MsgBox "X equals 25"
    ElseIf X = 30 Then
      MsgBox "X equals 30"
    End If
    In the above code, X = 25 or X = 30 is the only thing the IF..Then block will react to... for any other value, the If..Then block simply ignores it and execution resumes at the statement after the End If.
    Couldn't be more simple! Don't know why I couldn't see that. Thanks again!

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

    Default Re: Help with simple routine

    Quote Originally Posted by chobe View Post
    Couldn't be more simple! Don't know why I couldn't see that. Thanks again!
    If you have the opportunity to review the Select Case statement, you can verify that it is just as simple.

    Code:
        Select Case x
            Case 25
                MsgBox "x eq 25"
            Case 30
                MsgBox "x eq 30"
        End Select
    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
  •