For Each Statement still not working

My For Each Statement is not working as expected. Syntax is below. Is the
problem with the Application.CountA line? Basically if I enter duff
information on my worksheet, the code will detect the first occurance of an
error but ignores subsequent rows on the sheet with known errors, ie non
completion of cells K:N.
Can anyone help fix? The reply I received earlier this morning did not
help. I have stepped thru the code to view the values in the variables but
I'm not able to solve.

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, 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"

End If
End If
Next myCell

End With

End Sub

>>The reply I received earlier this morning did not help.

Unfortunate. But don't you think the polite thing to do is to thank Carlos
anyway, and maybe provide some more specifics to him or any other helper in
the original thread?

--
Jim
"Jacqui" <Jacqui@discussions.microsoft.com> wrote in message
news:38EE8BD0-4804-4913-B025-79ECEC4DF86F@microsoft.com...
| My For Each Statement is not working as expected. Syntax is below. Is
the
| problem with the Application.CountA line? Basically if I enter duff
| information on my worksheet, the code will detect the first occurance of
an
| error but ignores subsequent rows on the sheet with known errors, ie non
| completion of cells K:N.
| Can anyone help fix? The reply I received earlier this morning did not
| help. I have stepped thru the code to view the values in the variables
but
| I'm not able to solve.
|
| 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, 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"
|
| End If
| End If
| Next myCell
|
| End With
|
| End Sub
|

On the occasions when I have replied to an existing thread usually to thank
the contributor but also to notify them that there are unresolved issues in
the answer given I have received no further follow-up. It is not my usual
practice to create new messages unnecessarily but I have been trying to code
this for the last week without success. It is difficult to provide feedback
given that I'm no VBA expert.

"Jim Rech" wrote:

> >>The reply I received earlier this morning did not help.
>
> Unfortunate. But don't you think the polite thing to do is to thank Carlos
> anyway, and maybe provide some more specifics to him or any other helper in
> the original thread?
>
> --
> Jim
> "Jacqui" <Jacqui@discussions.microsoft.com> wrote in message
> news:38EE8BD0-4804-4913-B025-79ECEC4DF86F@microsoft.com...
> | My For Each Statement is not working as expected. Syntax is below. Is
> the
> | problem with the Application.CountA line? Basically if I enter duff
> | information on my worksheet, the code will detect the first occurance of
> an
> | error but ignores subsequent rows on the sheet with known errors, ie non
> | completion of cells K:N.
> | Can anyone help fix? The reply I received earlier this morning did not
> | help. I have stepped thru the code to view the values in the variables
> but
> | I'm not able to solve.
> |
> | 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, 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"
> |
> | End If
> | End If
> | Next myCell
> |
> | End With
> |
> | End Sub
> |
>
>
>

Hi Jacqui

I try simulate your code with dummy data and the code run as expected.
When debug your code the countA give the right value?

Try full sintax for excel funtion
-If Application.WorksheetFunction.CountA(myRngToCheck) <>
myRngToCheck.Cells.Count Then-

Carlos
"Jacqui" <Jacqui@discussions.microsoft.com> wrote in message
news:38EE8BD0-4804-4913-B025-79ECEC4DF86F@microsoft.com...
> My For Each Statement is not working as expected. Syntax is below. Is
> the
> problem with the Application.CountA line? Basically if I enter duff
> information on my worksheet, the code will detect the first occurance of
> an
> error but ignores subsequent rows on the sheet with known errors, ie non
> completion of cells K:N.
> Can anyone help fix? The reply I received earlier this morning did not
> help. I have stepped thru the code to view the values in the variables
> but
> I'm not able to solve.
>
> 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, 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"
>
> End If
> End If
> Next myCell
>
> End With
>
> End Sub
>

Thank you Carlos - I've established the problem it wasn't a coding issue
after all. There were some 'hidden' values in test sheet further down which
I hadn't noticed.

"Carlos" wrote:

> Hi Jacqui
>
> I try simulate your code with dummy data and the code run as expected.
> When debug your code the countA give the right value?
>
> Try full sintax for excel funtion
> -If Application.WorksheetFunction.CountA(myRngToCheck) <>
> myRngToCheck.Cells.Count Then-
>
> Carlos
> "Jacqui" <Jacqui@discussions.microsoft.com> wrote in message
> news:38EE8BD0-4804-4913-B025-79ECEC4DF86F@microsoft.com...
> > My For Each Statement is not working as expected. Syntax is below. Is
> > the
> > problem with the Application.CountA line? Basically if I enter duff
> > information on my worksheet, the code will detect the first occurance of
> > an
> > error but ignores subsequent rows on the sheet with known errors, ie non
> > completion of cells K:N.
> > Can anyone help fix? The reply I received earlier this morning did not
> > help. I have stepped thru the code to view the values in the variables
> > but
> > I'm not able to solve.
> >
> > 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, 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"
> >
> > End If
> > End If
> > Next myCell
> >
> > End With
> >
> > End Sub
> >
>
>
>

Your code worked ok for me.

I'm gonna guess that it's something in the data--maybe you don't have the cell
formatted correctly or maybe the cell isn't empty.

Remember that a cell that evaluates to "" isn't empty--or if that cell that
evaluated to "" was converted to values, then it's still not empty.

maybe this:
If IsEmpty(myCell.Value) = False ...
should be
if mycell.value <> "" ...
or
if trim(mycell.value) <> "" ...

And you should also declare your variables...

Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim myRngToCheck As Range





Jacqui wrote:
>
> My For Each Statement is not working as expected. Syntax is below. Is the
> problem with the Application.CountA line? Basically if I enter duff
> information on my worksheet, the code will detect the first occurance of an
> error but ignores subsequent rows on the sheet with known errors, ie non
> completion of cells K:N.
> Can anyone help fix? The reply I received earlier this morning did not
> help. I have stepped thru the code to view the values in the variables but
> I'm not able to solve.
>
> 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, 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"
>
> End If
> End If
> Next myCell
>
> End With
>
> End Sub

--

Dave Peterson

Dave

Thanks for your reply. I've established the problem wasn't VBA related at
all, just some hidden values I hadn't noticed. As regards the variables
these were declared as Public.

Thanks
Jacqui

"Dave Peterson" wrote:

> Your code worked ok for me.
>
> I'm gonna guess that it's something in the data--maybe you don't have the cell
> formatted correctly or maybe the cell isn't empty.
>
> Remember that a cell that evaluates to "" isn't empty--or if that cell that
> evaluated to "" was converted to values, then it's still not empty.
>
> maybe this:
> If IsEmpty(myCell.Value) = False ...
> should be
> if mycell.value <> "" ...
> or
> if trim(mycell.value) <> "" ...
>
> And you should also declare your variables...
>
> Dim wks As Worksheet
> Dim myRng As Range
> Dim myCell As Range
> Dim myRngToCheck As Range
>
>
>
>
>
> Jacqui wrote:
> >
> > My For Each Statement is not working as expected. Syntax is below. Is the
> > problem with the Application.CountA line? Basically if I enter duff
> > information on my worksheet, the code will detect the first occurance of an
> > error but ignores subsequent rows on the sheet with known errors, ie non
> > completion of cells K:N.
> > Can anyone help fix? The reply I received earlier this morning did not
> > help. I have stepped thru the code to view the values in the variables but
> > I'm not able to solve.
> >
> > 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, 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"
> >
> > End If
> > End If
> > Next myCell
> >
> > End With
> >
> > End Sub
>
> --
>
> Dave Peterson
>