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,

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" wrote in message
...
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

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" wrote in message
...
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

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

Paul

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

" 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

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

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" wrote in message
...
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