Solved

Exit Main Sub

Posted on 2011-09-07
31
402 Views
Last Modified: 2012-05-12
i've a worksheet change event in which there'r many sub routines, one of the sub routine is a loop, if a condition is not met in the sub routine code, i want to exit that particular loop. i use exit sub , but it only exits that sub routine and not the loop.
0
Comment
Question by:NYQuicksale
  • 11
  • 10
  • 4
  • +2
31 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 36497121
What kind of loop is it?

Exit For?

Exit Do?
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36497203
the main loop A contain another loopB
Sub loopB()
Dim cel As Range
Dim mySheet As Worksheet

Set mySheet = Sheets("sheet1")
For Each cel In Sheets("URLs").Range("A" & Sheets("URLs").Range("A1").End(xlDown).Row & ":A" & Sheets("URLs").Range("A" & Rows.Count).End(xlUp).Row)
mySheet.Range("A1") = cel.Value
Next cel
End Sub

Open in new window


in this LoopB, there's subroutine of worksheet change event.in that worksheet change , if a condition is not met,then  i want to exit loopB.
i use exit sub, then it exits that worksheet change, while i want to exit loop B
0
 
LVL 12

Expert Comment

by:rlandquist
ID: 36497327
You can use the Exit Do statement.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 1

Author Comment

by:NYQuicksale
ID: 36497397
the loop is not in the code, the loop is separate and the other code is in a worksheet change, how can i use exit do, when there's no loop here
0
 
LVL 12

Expert Comment

by:rlandquist
ID: 36497428
If I understand you correctly, you would have to set a value in the sub that the loop checks for and if it is there, exit the loop.
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36497457
Dim dcell As Range, cellbelowd As Range
            Set dcell = Worksheets("sheet1").Cells.Find("Document", , xlValues, xlWhole, , , False)
            If Not dcell Is Nothing Then
                If dcell.Value = "" Then
            With Sheets("info output")
            With .Cells(.Rows.Count, 8).End(xlUp).Offset(12)
                .Offset(, 1).Value = Sheets("info").Range("A1").Value
            End With
            End With
            Call finfo
            Exit Sub

Open in new window



instead of exit sub, i want to exit loopB, and loopB is in a module, while this code is in worksheet change
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 36497677
Define a global Boolean variable. Set it where the error occurs and check for it in the loop.
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36497743
there's no error, and will u plz be so kind to explain where to put the global bolean and what actually that is
0
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 36497777
I do VB6 so perhaps this doesn't apply, but in VB6 I would put

Public gbConditionMet As Boolean

in the module and then where the "condition is not met" do

gbConditionMet = False

You can then say

If not gbConditionMet Then...
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36497849
yes and perhaps i cant get any help today
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 36497882
What do you mean when you say "yes and perhaps i cant get any help today". I'm particularly asking about the "yes" part.
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36497901
I do VB6 so perhaps this doesn't apply
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36498037
Interesting attitude for one needing help.
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36498079
i didn't mean to show an attitude, if it seemed so, i'm sorry, i never intended
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36498140
Well you have your answer, so I'm not really sure what your last couple of posts meant.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 36498656
@rorya: Should NYQuicksale be able to do what I suggested (global variable) in post ID 36497777?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36498786
Yes - that should work fine.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 36498833
@NYQuicksale: So given rorya's answer to my question, what  didn't work for you?
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36502554
Mr.Martin in your Post ID: 36497777, i understood nothing, where to put it and where not,

Public gbConditionMet As Boolean

in the module and then where the "condition is not met" do

gbConditionMet = False

You can then say

If not gbConditionMet Then...

as in my above post ID: 36497743 i have already requested u to explain what this means and where to use it, as i no nothing about global boolean.
i've also given u the part of the code, if u can explain, i would appreciate that

how to use it in my  above sub, will u be so considerate to explain
0
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 36502688
1. Click the 'Insert' menu item and then select Module.

2. At the top of the module add Public gbConditionMet As Boolean. (A boolean variable has two values which are True and False)

3. You mention a condition that is not being met. In the code where you discover that the condition is not being met, add the line gbConditionMet = False

4. Then I assume you want to exit LoopB so change that code to

Sub loopB()
Dim cel As Range
Dim mySheet As Worksheet

Set mySheet = Sheets("sheet1")
For Each cel In Sheets("URLs").Range("A" & Sheets("URLs").Range("A1").End(xlDown).Row & ":A" & Sheets("URLs").Range("A" & Rows.Count).End(xlUp).Row)
If gbConditionNotMet Then Exit Sub 'Add this line
mySheet.Range("A1") = cel.Value
Next cel
End Sub
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36502824
that's what i'm saying, the condition not met line of code is not in module, its in the sheet, i've a worksheet change event, in that worksheet change event code, there's a line of code, where condition is not met, its not in a module, when a condition is not met here, i want to exit loopb, which is in module, can i use global boolean in a sheet rather than module?
0
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 36502869
Okay, one last try…

A variable that is in a code module (see my steps 1 and 2) can be referred to from anyplace in your project, so you can set it to False whenever and wherever you find the condition not being met (see my step 3) and check for the value of the boolean variable being False wherever you need to.
0
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 36503225
In an Inserted Module of your excel document -
Right Click in Project Window  - Select Insert - Select Module...

Global bExitNow As Boolean

Modify your existing Code:

Sub loopB()
    Dim cel As Range
    Dim mySheet As Worksheet

    Set mySheet = Sheets("sheet1")
    bExitNow = false	
    For Each cel In Sheets("URLs").Range("A" & Sheets("URLs").Range("A1").End(xlDown).Row & ":A" & Sheets("URLs").Range("A" & Rows.Count).End(xlUp).Row)
        mySheet.Range("A1") = cel.Value
        if bExitNow then Exit For
    Next cel

End Sub



' your worksheet change code

Private Sub Worksheet_Change(ByVal Target As Range)
            ' your code here
            
            ' perhaps some sort of Loop
            
            If Condition Then
               bExitNow = True
               Exit Sub
            End If
            
            ' other code to run if you do not exit
            
End Sub

Open in new window

0
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 36503231
If that works for you then please assign pts to MartinLiss :)
0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36503497
if i do not exit, meaning if the condition is false, dont u think i should use else, i mean something to differentiate after i use exit sub line

 i should use my rest of the code without using else?? if i do so, its exiting the loop in other condition as well
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 36503567
Can you show me the code where you think there should be an 'Else'?
0
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 36503589
I do not know what code you have in WorkSheet_Change so I do not know where you want to check and exit the sub.

an Else may not be necessary here unless you want to do several things even if the condition is true.
If you want to leave as soon as the condition is True then you do not need an else.

the (' perhaps some sort of loop) is only indicating that as you mentioned earlier you had two loops...
if that is the case..


Private Sub Worksheet_Change(ByVal Target As Range)
            ' your code here
            
            For Each of These in Those
               If Condition Then
                  bExitNow = True
                  Exit Sub
               End If
            
            Next

            ' other code to run if you do not exit
            
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:NYQuicksale
ID: 36503631
u've been so considerating Brook, thanks for all the help
0
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 36503684
I appreciate your comments but would rather that you award Martin as all I did was re-state his response which was the correct answer.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 36503760
Thanks Brook.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question