Solved

Exit Main Sub

Posted on 2011-09-07
31
398 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 45

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
 
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 45

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 45

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 45

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

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 45

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 45

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 45

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 45

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 45

Expert Comment

by:Martin Liss
ID: 36503760
Thanks Brook.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now