Help with For Next and Exit For

[Jacqui]

I have the following syntax which runs a For Each Next loop. This works
absolutely fine but I'd now like to introduce another sub called
Hierarchies_Check (this is essentially the same data check but on a different
worksheet) only if the code passes through the For Each statement without
finding any exceptions. I have an Exit For statement to quit the routine
(which allows the user to make the corrections one at a time rather than
being inundated with msgbox after msgbox if several exceptions are found).
I understand the Exit For will transfer control to the statement following
the Next statement therefore if I insert Hierarchies_Check sub after the Next
myCell line it will run the next routine before I'd like it to. I would
like the first For Each loop to be satisfied on the first worksheet before it
moves to the next worksheet to check. Can anyone suggest a workaround my code
is below.
Many thanks
Jacqui
PS Thank you to everyone who has helped me compile the code so far, you know
who you are so thanks for your input.

Option Explicit

Public myCell As Range
Public myRng As Range
Public wks As Worksheet
Public myRngToCheck As Range

Sub Qualifiers_Check()

Set wks = ActiveWorkbook.Worksheets("Part B - Coding Details")

With wks

Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp))

Set myRng = myRng.Resize(myRng.Count - 1)

For Each myCell In myRng.Cells
If IsEmpty(myCell.Value) = False And Not myCell.Font.Bold Then
Set myRngToCheck = .Cells(myCell.Row, "k").Resize(1, 5)
If Application.CountA(myRngToCheck) <> myRngToCheck.Cells.Count
Then
Beep

Msgbox "You have not supplied all the relevant information
for this Segment type in Row " _
& myCell.Row & " on the Coding Details Sheet -
PLEASE ENTER ALL DETAILS" _
, 48, "Change Request Form Error Checks - SECTIONS A - E"

Worksheets("Part B - Coding Details").Select
myCell.Select

Exit For

End If
End If
Next myCell

'Hierarchies_Check (doesn't run in the right place)


End With


End Sub

Sub Hierarchies_Check()

Set wks = ActiveWorkbook.Worksheets("Part C - Hierarchies")


With wks

Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp))

Set myRng = myRng.Resize(myRng.Count - 1)

For Each myCell In myRng.Cells
If IsEmpty(myCell.Value) = False And Not myCell.Font.Bold Then
Set myRngToCheck = .Cells(myCell.Row, "l").Resize(1, 4)
If Application.CountA(myRngToCheck) <> myRngToCheck.Cells.Count
Then
Beep

Msgbox "You have not supplied all the relevant information
for this Segment type in Row " _
& myCell.Row & " on the Coding Details Sheet -
PLEASE ENTER ALL DETAILS" _
, 48, "Change Request Form Error Checks - SECTION F
HIERARCHIES"

Worksheets("Part C - Hierarchies").Select

myCell.Select


Exit For

End If
End If
Next myCell

End With


End Sub

[Bob Phillips]

Jacqui,

In pseudo code you have

With wks
For Each Cell In Range
If Cell Not Empty And Cell NotBold Then
If Range_To_Check Has Empty Cells
Error
Exit For
End If
End If
Next Cell
End With


Presumably this test should take place when the range has no empty cells, so
it would then be


With wks
For Each Cell In Range
If Cell Not Empty And Cell NotBold Then
If Range_To_Check Has Empty Cells
Error
Exit For
Else
Cal Hierarchies_Check
End If
End If
Next Cell
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jacqui" <[email protected]> wrote in message
news:[email protected]...
> I have the following syntax which runs a For Each Next loop. This works
> absolutely fine but I'd now like to introduce another sub called
> Hierarchies_Check (this is essentially the same data check but on a
different
> worksheet) only if the code passes through the For Each statement without
> finding any exceptions. I have an Exit For statement to quit the routine
> (which allows the user to make the corrections one at a time rather than
> being inundated with msgbox after msgbox if several exceptions are found).
> I understand the Exit For will transfer control to the statement following
> the Next statement therefore if I insert Hierarchies_Check sub after the
Next
> myCell line it will run the next routine before I'd like it to. I would
> like the first For Each loop to be satisfied on the first worksheet before
it
> moves to the next worksheet to check. Can anyone suggest a workaround my
code
> is below.
> Many thanks
> Jacqui
> PS Thank you to everyone who has helped me compile the code so far, you
know
> who you are so thanks for your input.
>
> Option Explicit
>
> Public myCell As Range
> Public myRng As Range
> Public wks As Worksheet
> Public myRngToCheck As Range
>
> Sub Qualifiers_Check()
>
> Set wks = ActiveWorkbook.Worksheets("Part B - Coding Details")
>
> With wks
>
> Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp))
>
> Set myRng = myRng.Resize(myRng.Count - 1)
>
> For Each myCell In myRng.Cells
> If IsEmpty(myCell.Value) = False And Not myCell.Font.Bold Then
> Set myRngToCheck = .Cells(myCell.Row, "k").Resize(1, 5)
> If Application.CountA(myRngToCheck) <> myRngToCheck.Cells.Count
> Then
> Beep
>
> Msgbox "You have not supplied all the relevant information
> for this Segment type in Row " _
> & myCell.Row & " on the Coding Details Sheet -
> PLEASE ENTER ALL DETAILS" _
> , 48, "Change Request Form Error Checks - SECTIONS A - E"
>
> Worksheets("Part B - Coding Details").Select
> myCell.Select
>
> Exit For
>
> End If
> End If
> Next myCell
>
> 'Hierarchies_Check (doesn't run in the right place)
>
>
> End With
>
>
> End Sub
>
> Sub Hierarchies_Check()
>
> Set wks = ActiveWorkbook.Worksheets("Part C - Hierarchies")
>
>
> With wks
>
> Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp))
>
> Set myRng = myRng.Resize(myRng.Count - 1)
>
> For Each myCell In myRng.Cells
> If IsEmpty(myCell.Value) = False And Not myCell.Font.Bold Then
> Set myRngToCheck = .Cells(myCell.Row, "l").Resize(1, 4)
> If Application.CountA(myRngToCheck) <> myRngToCheck.Cells.Count
> Then
> Beep
>
> Msgbox "You have not supplied all the relevant information
> for this Segment type in Row " _
> & myCell.Row & " on the Coding Details Sheet -
> PLEASE ENTER ALL DETAILS" _
> , 48, "Change Request Form Error Checks - SECTION F
> HIERARCHIES"
>
> Worksheets("Part C - Hierarchies").Select
>
> myCell.Select
>
>
> Exit For
>
> End If
> End If
> Next myCell
>
> End With
>
>
> End Sub
>
>
>
>
>

[[email protected]]

Hi
In general you would use a Boolean variable to flag when all the checks
are done and are OK. Now check the value of that varaiable to see if
you should run your Hierarchies_Check.

eg. Dim OKFlag as Boolean

OKFlag = True 'everything OK so far
For Each myCell In myRng.Cells
.....
If Application.CountA(myRngToCheck) <> myRngToCheck.Cells.Count Then
.....
OKFlag = False
Exit for
......
end if
....
next Cell
'If OKFlag is still true then run your next sub
If OKFlag then
Hierarchies_Check
end if

[Jacqui]

Thank You

"Bob Phillips" wrote:

> Jacqui,
>
> In pseudo code you have
>
> With wks
> For Each Cell In Range
> If Cell Not Empty And Cell NotBold Then
> If Range_To_Check Has Empty Cells
> Error
> Exit For
> End If
> End If
> Next Cell
> End With
>
>
> Presumably this test should take place when the range has no empty cells, so
> it would then be
>
>
> With wks
> For Each Cell In Range
> If Cell Not Empty And Cell NotBold Then
> If Range_To_Check Has Empty Cells
> Error
> Exit For
> Else
> Cal Hierarchies_Check
> End If
> End If
> Next Cell
> End With
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Jacqui" <[email protected]> wrote in message
> news:[email protected]...
> > I have the following syntax which runs a For Each Next loop. This works
> > absolutely fine but I'd now like to introduce another sub called
> > Hierarchies_Check (this is essentially the same data check but on a
> different
> > worksheet) only if the code passes through the For Each statement without
> > finding any exceptions. I have an Exit For statement to quit the routine
> > (which allows the user to make the corrections one at a time rather than
> > being inundated with msgbox after msgbox if several exceptions are found).
> > I understand the Exit For will transfer control to the statement following
> > the Next statement therefore if I insert Hierarchies_Check sub after the
> Next
> > myCell line it will run the next routine before I'd like it to. I would
> > like the first For Each loop to be satisfied on the first worksheet before
> it
> > moves to the next worksheet to check. Can anyone suggest a workaround my
> code
> > is below.
> > Many thanks
> > Jacqui
> > PS Thank you to everyone who has helped me compile the code so far, you
> know
> > who you are so thanks for your input.
> >
> > Option Explicit
> >
> > Public myCell As Range
> > Public myRng As Range
> > Public wks As Worksheet
> > Public myRngToCheck As Range
> >
> > Sub Qualifiers_Check()
> >
> > Set wks = ActiveWorkbook.Worksheets("Part B - Coding Details")
> >
> > With wks
> >
> > Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp))
> >
> > Set myRng = myRng.Resize(myRng.Count - 1)
> >
> > For Each myCell In myRng.Cells
> > If IsEmpty(myCell.Value) = False And Not myCell.Font.Bold Then
> > Set myRngToCheck = .Cells(myCell.Row, "k").Resize(1, 5)
> > If Application.CountA(myRngToCheck) <> myRngToCheck.Cells.Count
> > Then
> > Beep
> >
> > Msgbox "You have not supplied all the relevant information
> > for this Segment type in Row " _
> > & myCell.Row & " on the Coding Details Sheet -
> > PLEASE ENTER ALL DETAILS" _
> > , 48, "Change Request Form Error Checks - SECTIONS A - E"
> >
> > Worksheets("Part B - Coding Details").Select
> > myCell.Select
> >
> > Exit For
> >
> > End If
> > End If
> > Next myCell
> >
> > 'Hierarchies_Check (doesn't run in the right place)
> >
> >
> > End With
> >
> >
> > End Sub
> >
> > Sub Hierarchies_Check()
> >
> > Set wks = ActiveWorkbook.Worksheets("Part C - Hierarchies")
> >
> >
> > With wks
> >
> > Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp))
> >
> > Set myRng = myRng.Resize(myRng.Count - 1)
> >
> > For Each myCell In myRng.Cells
> > If IsEmpty(myCell.Value) = False And Not myCell.Font.Bold Then
> > Set myRngToCheck = .Cells(myCell.Row, "l").Resize(1, 4)
> > If Application.CountA(myRngToCheck) <> myRngToCheck.Cells.Count
> > Then
> > Beep
> >
> > Msgbox "You have not supplied all the relevant information
> > for this Segment type in Row " _
> > & myCell.Row & " on the Coding Details Sheet -
> > PLEASE ENTER ALL DETAILS" _
> > , 48, "Change Request Form Error Checks - SECTION F
> > HIERARCHIES"
> >
> > Worksheets("Part C - Hierarchies").Select
> >
> > myCell.Select
> >
> >
> > Exit For
> >
> > End If
> > End If
> > Next myCell
> >
> > End With
> >
> >
> > End Sub
> >
> >
> >
> >
> >
>
>
>

[Jacqui]

Paul

Thanks for your reply. This is really neat. I like it!
Thanks again Jacqui

"[email protected]" wrote:

> Hi
> In general you would use a Boolean variable to flag when all the checks
> are done and are OK. Now check the value of that varaiable to see if
> you should run your Hierarchies_Check.
>
> eg. Dim OKFlag as Boolean
>
> OKFlag = True 'everything OK so far
> For Each myCell In myRng.Cells
> .....
> If Application.CountA(myRngToCheck) <> myRngToCheck.Cells.Count Then
> .....
> OKFlag = False
> Exit for
> ......
> end if
> ....
> next Cell
> 'If OKFlag is still true then run your next sub
> If OKFlag then
> Hierarchies_Check
> end if
>
>

[Jacqui]

Bob

Thanks for your reply. This almost worked (it still called the
Hierarchies_Check if a subsequent row was incomplete). However, I did like
your pseudo code it should be used more often.
My routine was fixed with the OKFlag suggestion provided by Paul.

Many thanks though for replying.
Jacqui

"Jacqui" wrote:

> I have the following syntax which runs a For Each Next loop. This works
> absolutely fine but I'd now like to introduce another sub called
> Hierarchies_Check (this is essentially the same data check but on a different
> worksheet) only if the code passes through the For Each statement without
> finding any exceptions. I have an Exit For statement to quit the routine
> (which allows the user to make the corrections one at a time rather than
> being inundated with msgbox after msgbox if several exceptions are found).
> I understand the Exit For will transfer control to the statement following
> the Next statement therefore if I insert Hierarchies_Check sub after the Next
> myCell line it will run the next routine before I'd like it to. I would
> like the first For Each loop to be satisfied on the first worksheet before it
> moves to the next worksheet to check. Can anyone suggest a workaround my code
> is below.
> Many thanks
> Jacqui
> PS Thank you to everyone who has helped me compile the code so far, you know
> who you are so thanks for your input.
>
> Option Explicit
>
> Public myCell As Range
> Public myRng As Range
> Public wks As Worksheet
> Public myRngToCheck As Range
>
> Sub Qualifiers_Check()
>
> Set wks = ActiveWorkbook.Worksheets("Part B - Coding Details")
>
> With wks
>
> Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp))
>
> Set myRng = myRng.Resize(myRng.Count - 1)
>
> For Each myCell In myRng.Cells
> If IsEmpty(myCell.Value) = False And Not myCell.Font.Bold Then
> Set myRngToCheck = .Cells(myCell.Row, "k").Resize(1, 5)
> If Application.CountA(myRngToCheck) <> myRngToCheck.Cells.Count
> Then
> Beep
>
> Msgbox "You have not supplied all the relevant information
> for this Segment type in Row " _
> & myCell.Row & " on the Coding Details Sheet -
> PLEASE ENTER ALL DETAILS" _
> , 48, "Change Request Form Error Checks - SECTIONS A - E"
>
> Worksheets("Part B - Coding Details").Select
> myCell.Select
>
> Exit For
>
> End If
> End If
> Next myCell
>
> 'Hierarchies_Check (doesn't run in the right place)
>
>
> End With
>
>
> End Sub
>
> Sub Hierarchies_Check()
>
> Set wks = ActiveWorkbook.Worksheets("Part C - Hierarchies")
>
>
> With wks
>
> Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp))
>
> Set myRng = myRng.Resize(myRng.Count - 1)
>
> For Each myCell In myRng.Cells
> If IsEmpty(myCell.Value) = False And Not myCell.Font.Bold Then
> Set myRngToCheck = .Cells(myCell.Row, "l").Resize(1, 4)
> If Application.CountA(myRngToCheck) <> myRngToCheck.Cells.Count
> Then
> Beep
>
> Msgbox "You have not supplied all the relevant information
> for this Segment type in Row " _
> & myCell.Row & " on the Coding Details Sheet -
> PLEASE ENTER ALL DETAILS" _
> , 48, "Change Request Form Error Checks - SECTION F
> HIERARCHIES"
>
> Worksheets("Part C - Hierarchies").Select
>
> myCell.Select
>
>
> Exit For
>
> End If
> End If
> Next myCell
>
> End With
>
>
> End Sub
>
>
>
>
>

[Bob Phillips]

OK, I see you want the check only if all is okay. The flag is the way to go
then.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jacqui" <[email protected]> wrote in message
news:[email protected]...
> Bob
>
> Thanks for your reply. This almost worked (it still called the
> Hierarchies_Check if a subsequent row was incomplete). However, I did
like
> your pseudo code it should be used more often.
> My routine was fixed with the OKFlag suggestion provided by Paul.
>
> Many thanks though for replying.
> Jacqui
>
> "Jacqui" wrote:
>
> > I have the following syntax which runs a For Each Next loop. This works
> > absolutely fine but I'd now like to introduce another sub called
> > Hierarchies_Check (this is essentially the same data check but on a
different
> > worksheet) only if the code passes through the For Each statement
without
> > finding any exceptions. I have an Exit For statement to quit the
routine
> > (which allows the user to make the corrections one at a time rather than
> > being inundated with msgbox after msgbox if several exceptions are
found).
> > I understand the Exit For will transfer control to the statement
following
> > the Next statement therefore if I insert Hierarchies_Check sub after the
Next
> > myCell line it will run the next routine before I'd like it to. I
would
> > like the first For Each loop to be satisfied on the first worksheet
before it
> > moves to the next worksheet to check. Can anyone suggest a workaround my
code
> > is below.
> > Many thanks
> > Jacqui
> > PS Thank you to everyone who has helped me compile the code so far, you
know
> > who you are so thanks for your input.
> >
> > Option Explicit
> >
> > Public myCell As Range
> > Public myRng As Range
> > Public wks As Worksheet
> > Public myRngToCheck As Range
> >
> > Sub Qualifiers_Check()
> >
> > Set wks = ActiveWorkbook.Worksheets("Part B - Coding Details")
> >
> > With wks
> >
> > Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp))
> >
> > Set myRng = myRng.Resize(myRng.Count - 1)
> >
> > For Each myCell In myRng.Cells
> > If IsEmpty(myCell.Value) = False And Not myCell.Font.Bold Then
> > Set myRngToCheck = .Cells(myCell.Row, "k").Resize(1, 5)
> > If Application.CountA(myRngToCheck) <>
myRngToCheck.Cells.Count
> > Then
> > Beep
> >
> > Msgbox "You have not supplied all the relevant
information
> > for this Segment type in Row " _
> > & myCell.Row & " on the Coding Details Sheet -
> > PLEASE ENTER ALL DETAILS" _
> > , 48, "Change Request Form Error Checks - SECTIONS A -
E"
> >
> > Worksheets("Part B - Coding Details").Select
> > myCell.Select
> >
> > Exit For
> >
> > End If
> > End If
> > Next myCell
> >
> > 'Hierarchies_Check (doesn't run in the right place)
> >
> >
> > End With
> >
> >
> > End Sub
> >
> > Sub Hierarchies_Check()
> >
> > Set wks = ActiveWorkbook.Worksheets("Part C - Hierarchies")
> >
> >
> > With wks
> >
> > Set myRng = .Range("c20", .Cells(.Rows.Count, "c").End(xlUp))
> >
> > Set myRng = myRng.Resize(myRng.Count - 1)
> >
> > For Each myCell In myRng.Cells
> > If IsEmpty(myCell.Value) = False And Not myCell.Font.Bold Then
> > Set myRngToCheck = .Cells(myCell.Row, "l").Resize(1, 4)
> > If Application.CountA(myRngToCheck) <>
myRngToCheck.Cells.Count
> > Then
> > Beep
> >
> > Msgbox "You have not supplied all the relevant
information
> > for this Segment type in Row " _
> > & myCell.Row & " on the Coding Details Sheet -
> > PLEASE ENTER ALL DETAILS" _
> > , 48, "Change Request Form Error Checks - SECTION F
> > HIERARCHIES"
> >
> > Worksheets("Part C - Hierarchies").Select
> >
> > myCell.Select
> >
> >
> > Exit For
> >
> > End If
> > End If
> > Next myCell
> >
> > End With
> >
> >
> > End Sub
> >
> >
> >
> >
> >