Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

Exit Main Sub

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
NYQuicksale
Asked:
NYQuicksale
  • 11
  • 10
  • 4
  • +2
4 Solutions
 
Martin LissRetired ProgrammerCommented:
What kind of loop is it?

Exit For?

Exit Do?
0
 
NYQuicksaleAuthor Commented:
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
 
rlandquistCommented:
You can use the Exit Do statement.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
NYQuicksaleAuthor Commented:
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
 
rlandquistCommented:
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
 
NYQuicksaleAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
Define a global Boolean variable. Set it where the error occurs and check for it in the loop.
0
 
NYQuicksaleAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
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
 
NYQuicksaleAuthor Commented:
yes and perhaps i cant get any help today
0
 
Martin LissRetired ProgrammerCommented:
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
 
NYQuicksaleAuthor Commented:
I do VB6 so perhaps this doesn't apply
0
 
Rory ArchibaldCommented:
Interesting attitude for one needing help.
0
 
NYQuicksaleAuthor Commented:
i didn't mean to show an attitude, if it seemed so, i'm sorry, i never intended
0
 
Rory ArchibaldCommented:
Well you have your answer, so I'm not really sure what your last couple of posts meant.
0
 
Martin LissRetired ProgrammerCommented:
@rorya: Should NYQuicksale be able to do what I suggested (global variable) in post ID 36497777?
0
 
Rory ArchibaldCommented:
Yes - that should work fine.
0
 
Martin LissRetired ProgrammerCommented:
@NYQuicksale: So given rorya's answer to my question, what  didn't work for you?
0
 
NYQuicksaleAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
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
 
NYQuicksaleAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
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
 
Brook BraswellApplication Development ManagerCommented:
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
 
Brook BraswellApplication Development ManagerCommented:
If that works for you then please assign pts to MartinLiss :)
0
 
NYQuicksaleAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
Can you show me the code where you think there should be an 'Else'?
0
 
Brook BraswellApplication Development ManagerCommented:
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
 
NYQuicksaleAuthor Commented:
u've been so considerating Brook, thanks for all the help
0
 
Brook BraswellApplication Development ManagerCommented:
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
 
Martin LissRetired ProgrammerCommented:
Thanks Brook.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 11
  • 10
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now