Exit Sub when For Condition is met

hyd1956

New Member
Joined
Jun 26, 2020
Messages
44
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I want my code to exit as soon as the condition of the if statement within the for loop are met and the message box appears (shown in yellow below).
When exit sub is added, it exits after the messagebox but the rest of my code does not run when the conditions are not met, and if I remove Exit sub then it continues to run when the conditions are met. Also moving Exit sub before Next x creates an error of Next without For.

Is there an easy fix for this problem?

Thank you

Rich (BB code):
If duplicatesheet4.Range("Y11").Value > duplicatesheet4.Range("Y16").Value Then
MsgBox ("There are not enough")
Exit Sub
Else
duplicatesheet4.Range("K2:V" & Lastrow).Value = duplicatesheet4.Range("K2:V" & Lastrow).Value
For x = 2 To duplicatesheet4.Range("K2" & Lastrow).End(xlUp).Row
        If duplicatesheet4.Range("K" & x).Value = "Investigation needed" Or duplicatesheet4.Range("K" & x).Value = "Please choose Y/N" Then MsgBox ("Please update all entries in column K using Y or N")
   Next x
Exit sub

Else

If duplicatesheet4.Range("Y11").Value <= duplicatesheet4.Range("Y16") Then

Call duplicatefindertransfer3
Call Assigned_duplicate
 
Last edited by a moderator:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,230
Office Version
  1. 365
Platform
  1. Windows
I haven't checked the code in detail but based on your question it should be as simple as using If and End If within the For - Next loop.
VBA Code:
For x = 2 To duplicatesheet4.Range("K2" & Lastrow).End(xlUp).Row
        If duplicatesheet4.Range("K" & x).Value = "Investigation needed" Or duplicatesheet4.Range("K" & x).Value = "Please choose Y/N" Then 
            MsgBox ("Please update all entries in column K using Y or N")
            Exit Sub
        End If
   Next x
 

hyd1956

New Member
Joined
Jun 26, 2020
Messages
44
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks I've tired but I get an error of End If without Block If - same issue when the code is in it own module too
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,230
Office Version
  1. 365
Platform
  1. Windows
That will be elsewhere in your code, there are block If's in your code that are not closed. Possibly you have ended them out of order.
 
Solution

hyd1956

New Member
Joined
Jun 26, 2020
Messages
44
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks tweaked the rest of the code and got this working as it should :)
 

Forum statistics

Threads
1,137,151
Messages
5,679,895
Members
419,861
Latest member
AceDaMace

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top