Hi,

I want to write a code to check whether errors exist in column E. I use the
following code:

Dim r as Interger
r=9
r=r+1
If iserror(cells(r,5)) then
Msgbox "Please fix error"
else


Thanks. Please help.

Sub FindErrors()
Dim myCell As Range
On Error GoTo NoErrors
For Each myCell In Range("E:E").SpecialCells(xlCellTypeFormulas, 16)
MsgBox "Cell " & myCell.Address & " has an error, so please fix it!"
Next myCell
Exit Sub
NoErrors:
MsgBox "No errors were found in column E."
End Sub

HTH,
Bernie
MS Excel MVP


"AccessHelp" wrote in message
...
Hi,

I want to write a code to check whether errors exist in column E. I use the
following code:

Dim r as Interger
r=9
r=r+1
If iserror(cells(r,5)) then
Msgbox "Please fix error"
else


Thanks. Please help.

Hi Bernie,

Thanks for your help. I need a different code. This code would not work
for me. Please help.

I am only testing the cells from E9 to E83. Those cells are password
protected and the values in those cells are looking at the cells from D9 to
d83 using vlookup. For example, if a user types in a wrong data in D9, E9
would return #N/A. I want to test E9 to E83 to make sure that we don't have
any #N/A.

Also I need it in IF statement. In my code, I am testing on several things
using IFs (so if that is correct, go on to another one.).

Thanks.

"Bernie Deitrick" wrote:

Sub FindErrors()
Dim myCell As Range
On Error GoTo NoErrors
For Each myCell In Range("E:E").SpecialCells(xlCellTypeFormulas, 16)
MsgBox "Cell " & myCell.Address & " has an error, so please fix it!"
Next myCell
Exit Sub
NoErrors:
MsgBox "No errors were found in column E."
End Sub

HTH,
Bernie
MS Excel MVP


"AccessHelp" wrote in message
...
Hi,

I want to write a code to check whether errors exist in column E. I use the
following code:

Dim r as Interger
r=9
r=r+1
If iserror(cells(r,5)) then
Msgbox "Please fix error"
else


Thanks. Please help.

Actually, it would work for you, but have it your way:

Sub FindErrors()
Dim myCell As Range
For Each myCell In Range("E9:E83")
If IsError(myCell.Value) Then
MsgBox "Cell " & myCell.Address & " has an error, so please fix it!"
' Other stuff here
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"AccessHelp" wrote in message
...
Hi Bernie,

Thanks for your help. I need a different code. This code would not work
for me. Please help.

I am only testing the cells from E9 to E83. Those cells are password
protected and the values in those cells are looking at the cells from D9 to
d83 using vlookup. For example, if a user types in a wrong data in D9, E9
would return #N/A. I want to test E9 to E83 to make sure that we don't have
any #N/A.

Also I need it in IF statement. In my code, I am testing on several things
using IFs (so if that is correct, go on to another one.).

Thanks.

"Bernie Deitrick" wrote:

Sub FindErrors()
Dim myCell As Range
On Error GoTo NoErrors
For Each myCell In Range("E:E").SpecialCells(xlCellTypeFormulas, 16)
MsgBox "Cell " & myCell.Address & " has an error, so please fix it!"
Next myCell
Exit Sub
NoErrors:
MsgBox "No errors were found in column E."
End Sub

HTH,
Bernie
MS Excel MVP


"AccessHelp" wrote in message
...
Hi,

I want to write a code to check whether errors exist in column E. I use the
following code:

Dim r as Interger
r=9
r=r+1
If iserror(cells(r,5)) then
Msgbox "Please fix error"
else


Thanks. Please help.

Hi Bernie,

Thanks again for your help. I still need help. Please see below of a
portion of my code.

If Not IsDate(Range("B8")) Then
MsgBox "Please enter a week ending date in cell B8."
ElseIf IsError(Range("K6")) Then
MsgBox "Please enter your employee # in cell C8."
ElseIf (That is where I want to check the values in E9:E83)
Msgbox (if there is an error, prompt message and skip the
code all the way to "ActiveWorkbook.Save". If no error, go through the rest
of code until "ActiveWorkbook.Save".)
Else
(Do the rest of code)
End If
ActiveWorkbook.Save
End Sub

Thanks again.


"Bernie Deitrick" wrote:

Actually, it would work for you, but have it your way:

Sub FindErrors()
Dim myCell As Range
For Each myCell In Range("E9:E83")
If IsError(myCell.Value) Then
MsgBox "Cell " & myCell.Address & " has an error, so please fix it!"
' Other stuff here
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"AccessHelp" wrote in message
...
Hi Bernie,

Thanks for your help. I need a different code. This code would not work
for me. Please help.

I am only testing the cells from E9 to E83. Those cells are password
protected and the values in those cells are looking at the cells from D9 to
d83 using vlookup. For example, if a user types in a wrong data in D9, E9
would return #N/A. I want to test E9 to E83 to make sure that we don't have
any #N/A.

Also I need it in IF statement. In my code, I am testing on several things
using IFs (so if that is correct, go on to another one.).

Thanks.

"Bernie Deitrick" wrote:

Sub FindErrors()
Dim myCell As Range
On Error GoTo NoErrors
For Each myCell In Range("E:E").SpecialCells(xlCellTypeFormulas, 16)
MsgBox "Cell " & myCell.Address & " has an error, so please fix it!"
Next myCell
Exit Sub
NoErrors:
MsgBox "No errors were found in column E."
End Sub

HTH,
Bernie
MS Excel MVP


"AccessHelp" wrote in message
...
Hi,

I want to write a code to check whether errors exist in column E. I use the
following code:

Dim r as Interger
r=9
r=r+1
If iserror(cells(r,5)) then
Msgbox "Please fix error"
else


Thanks. Please help.

Try something like the code below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim NeedInput As Boolean
Dim myCell As Range

NeedInput = Not IsDate(Range("B8").Value)

While NeedInput
Range("B8").Value = InputBox("What is the week ending date for cell B8?")
NeedInput = Not IsDate(Range("B8").Value)
Wend

NeedInput = IsError(Range("K6").Value)

While NeedInput
Range("C8").Value = InputBox("What is your employee # for cell C8.")
NeedInput = IsError(Range("K6").Value)
Wend

On Error GoTo NoErrors
Set myCell = Range("E9:E83").SpecialCells(xlCellTypeFormulas, 16)
MsgBox "There are errors in range E9:E83"
GoTo SaveCode:

NoErrors:

MsgBox "I'm going to do the rest of the code now"
'(Do the rest of code)
'Rest of code here

SaveCode:

ActiveWorkbook.Save
End Sub


"AccessHelp" wrote in message
...
Hi Bernie,

Thanks again for your help. I still need help. Please see below of a
portion of my code.

If Not IsDate(Range("B8")) Then
MsgBox "Please enter a week ending date in cell B8."
ElseIf IsError(Range("K6")) Then
MsgBox "Please enter your employee # in cell C8."
ElseIf (That is where I want to check the values in E9:E83)
Msgbox (if there is an error, prompt message and skip the
code all the way to "ActiveWorkbook.Save". If no error, go through the rest
of code until "ActiveWorkbook.Save".)
Else
(Do the rest of code)
End If
ActiveWorkbook.Save
End Sub

Thanks again.


"Bernie Deitrick" wrote:

Actually, it would work for you, but have it your way:

Sub FindErrors()
Dim myCell As Range
For Each myCell In Range("E9:E83")
If IsError(myCell.Value) Then
MsgBox "Cell " & myCell.Address & " has an error, so please fix it!"
' Other stuff here
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"AccessHelp" wrote in message
...
Hi Bernie,

Thanks for your help. I need a different code. This code would not work
for me. Please help.

I am only testing the cells from E9 to E83. Those cells are password
protected and the values in those cells are looking at the cells from D9 to
d83 using vlookup. For example, if a user types in a wrong data in D9, E9
would return #N/A. I want to test E9 to E83 to make sure that we don't have
any #N/A.

Also I need it in IF statement. In my code, I am testing on several things
using IFs (so if that is correct, go on to another one.).

Thanks.

"Bernie Deitrick" wrote:

Sub FindErrors()
Dim myCell As Range
On Error GoTo NoErrors
For Each myCell In Range("E:E").SpecialCells(xlCellTypeFormulas, 16)
MsgBox "Cell " & myCell.Address & " has an error, so please fix it!"
Next myCell
Exit Sub
NoErrors:
MsgBox "No errors were found in column E."
End Sub

HTH,
Bernie
MS Excel MVP


"AccessHelp" wrote in message
...
Hi,

I want to write a code to check whether errors exist in column E. I use the
following code:

Dim r as Interger
r=9
r=r+1
If iserror(cells(r,5)) then
Msgbox "Please fix error"
else


Thanks. Please help.