To Check Errors for a range of cells
 

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.

To Check Errors for a range of cells
 

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.

To Check Errors for a range of cells
 

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.

To Check Errors for a range of cells
 

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.

To Check Errors for a range of cells
 

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.

To Check Errors for a range of cells
 

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.

To Check Errors for a range of cells
 

Hi Bernie,

Thanks again for your help. The code is not working and is not checking any
errors for E9:E83. It took directly to the "No Errors:" section although
there are some errors in E10 and E12 (when I tested).

Is it possible that we stick to the IF structure in my previous message? As
you know, I am checking to make sure that they input a week ending date,
their employee # and no errors in E9:E83. Then do the rest with Else.

Thanks. Have a safe and nice holiday season!

"Bernie Deitrick" wrote:

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.

To Check Errors for a range of cells
 

Try this version, 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

For Each myCell In Range("E9:E83")
If IsError(myCell.Value) Then
MsgBox "Cell " & myCell.Address & " has an error, so please fix it!"
GoTo SaveCode:
End If
Next myCell

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. The code is not working and is not checking any
errors for E9:E83. It took directly to the "No Errors:" section although
there are some errors in E10 and E12 (when I tested).

Is it possible that we stick to the IF structure in my previous message? As
you know, I am checking to make sure that they input a week ending date,
their employee # and no errors in E9:E83. Then do the rest with Else.

Thanks. Have a safe and nice holiday season!

"Bernie Deitrick" wrote:

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.

To Check Errors for a range of cells
 

Bernie,

Thanks again for your help. I hope you had a nice Thanksgiving yesterday.
The last code that you gave to me works. Can you help me one more code?

What I like to do is I have a range of cells (I9:I33), and I like to check
whether it has 50, 51, or 53 in those cells if any of the cells (A9:A33) is
not blank. So (for example) if Cell A9 is not blank and Cell I9 is either
blank or non (50,51, or 53), then prompt the message "Please check the
Service # in Cell I9". A9:A33 are the date fields.

Below is a code that I wrote, and it's not working. This code will be an
addition to the code from you last time. As always, I appreciate your help.

Dim weCell as Range
Dim ServCell As Range
For Each weCell In Range("A9:A33")
If Not (IsEmpty(weCell.Text)) Then
For Each servCell In Range("I9:I33")
If servCell.Value = "" Then
MsgBox "Please check the Service # in cell " &
servCell.Address & "."
GoTo SaveWorkbook:
End If
Next servCell
GoTo NoErrors
End If
Next weCell




"Bernie Deitrick" wrote:

Try this version, 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

For Each myCell In Range("E9:E83")
If IsError(myCell.Value) Then
MsgBox "Cell " & myCell.Address & " has an error, so please fix it!"
GoTo SaveCode:
End If
Next myCell

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. The code is not working and is not checking any
errors for E9:E83. It took directly to the "No Errors:" section although
there are some errors in E10 and E12 (when I tested).

Is it possible that we stick to the IF structure in my previous message? As
you know, I am checking to make sure that they input a week ending date,
their employee # and no errors in E9:E83. Then do the rest with Else.

Thanks. Have a safe and nice holiday season!

"Bernie Deitrick" wrote:

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.

To Check Errors for a range of cells
 

Dim weCell As Range
Dim ServCell As Range
For Each weCell In Range("A9:A33")
If weCell.Text < "" Then
Set ServCell = Cells(weCell.Row, "I")
If ServCell.Value < 50 And _
ServCell.Value < 51 And _
ServCell.Value < 53 Then
MsgBox "Please check the Service # in cell " & _
ServCell.Address & "."
GoTo SaveWorkbook:
End If
GoTo NoErrors
End If
Next weCell


Not sure about when you actually want to go to SaveWorkbook or NoErrors: they seem a bit premature
where they are currently located. This seems more logical:

Dim weCell As Range
Dim ServCell As Range
For Each weCell In Range("A9:A33")
If weCell.Text < "" Then
Set ServCell = Cells(weCell.Row, "I")
If ServCell.Value < 50 And _
ServCell.Value < 51 And _
ServCell.Value < 53 Then
MsgBox "Please check the Service # in cell " & _
ServCell.Address & "."
GoTo SaveWorkbook:
End If
End If
Next weCell

GoTo NoErrors


HTH,
Bernie
MS Excel MVP


"AccessHelp" wrote in message
...
Bernie,

Thanks again for your help. I hope you had a nice Thanksgiving yesterday.
The last code that you gave to me works. Can you help me one more code?

What I like to do is I have a range of cells (I9:I33), and I like to check
whether it has 50, 51, or 53 in those cells if any of the cells (A9:A33) is
not blank. So (for example) if Cell A9 is not blank and Cell I9 is either
blank or non (50,51, or 53), then prompt the message "Please check the
Service # in Cell I9". A9:A33 are the date fields.

Below is a code that I wrote, and it's not working. This code will be an
addition to the code from you last time. As always, I appreciate your help.

Dim weCell as Range
Dim ServCell As Range
For Each weCell In Range("A9:A33")
If Not (IsEmpty(weCell.Text)) Then
For Each servCell In Range("I9:I33")
If servCell.Value = "" Then
MsgBox "Please check the Service # in cell " &
servCell.Address & "."
GoTo SaveWorkbook:
End If
Next servCell
GoTo NoErrors
End If
Next weCell




"Bernie Deitrick" wrote:

Try this version, 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

For Each myCell In Range("E9:E83")
If IsError(myCell.Value) Then
MsgBox "Cell " & myCell.Address & " has an error, so please fix it!"
GoTo SaveCode:
End If
Next myCell

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. The code is not working and is not checking any
errors for E9:E83. It took directly to the "No Errors:" section although
there are some errors in E10 and E12 (when I tested).

Is it possible that we stick to the IF structure in my previous message? As
you know, I am checking to make sure that they input a week ending date,
their employee # and no errors in E9:E83. Then do the rest with Else.

Thanks. Have a safe and nice holiday season!

"Bernie Deitrick" wrote:

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.

To Check Errors for a range of cells
 

Good morning Bernie,

Sorry that I couldn't respond back to your soon! I was just able to test it.

Thanks again for your help. The code works. I used the second code, and I
made a minor change to your code FROM If weCell.Text < "" Then TO If
weCell.Text < " " Then. The original one was looking up the I cell even
though nothing is in the A cell.

As always, I appreciate your help. Have a nice and safe holiday season!!!


"Bernie Deitrick" wrote:

Dim weCell As Range
Dim ServCell As Range
For Each weCell In Range("A9:A33")
If weCell.Text < "" Then
Set ServCell = Cells(weCell.Row, "I")
If ServCell.Value < 50 And _
ServCell.Value < 51 And _
ServCell.Value < 53 Then
MsgBox "Please check the Service # in cell " & _
ServCell.Address & "."
GoTo SaveWorkbook:
End If
GoTo NoErrors
End If
Next weCell


Not sure about when you actually want to go to SaveWorkbook or NoErrors: they seem a bit premature
where they are currently located. This seems more logical:

Dim weCell As Range
Dim ServCell As Range
For Each weCell In Range("A9:A33")
If weCell.Text < "" Then
Set ServCell = Cells(weCell.Row, "I")
If ServCell.Value < 50 And _
ServCell.Value < 51 And _
ServCell.Value < 53 Then
MsgBox "Please check the Service # in cell " & _
ServCell.Address & "."
GoTo SaveWorkbook:
End If
End If
Next weCell

GoTo NoErrors


HTH,
Bernie
MS Excel MVP


"AccessHelp" wrote in message
...
Bernie,

Thanks again for your help. I hope you had a nice Thanksgiving yesterday.
The last code that you gave to me works. Can you help me one more code?

What I like to do is I have a range of cells (I9:I33), and I like to check
whether it has 50, 51, or 53 in those cells if any of the cells (A9:A33) is
not blank. So (for example) if Cell A9 is not blank and Cell I9 is either
blank or non (50,51, or 53), then prompt the message "Please check the
Service # in Cell I9". A9:A33 are the date fields.

Below is a code that I wrote, and it's not working. This code will be an
addition to the code from you last time. As always, I appreciate your help.

Dim weCell as Range
Dim ServCell As Range
For Each weCell In Range("A9:A33")
If Not (IsEmpty(weCell.Text)) Then
For Each servCell In Range("I9:I33")
If servCell.Value = "" Then
MsgBox "Please check the Service # in cell " &
servCell.Address & "."
GoTo SaveWorkbook:
End If
Next servCell
GoTo NoErrors
End If
Next weCell




"Bernie Deitrick" wrote:

Try this version, 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

For Each myCell In Range("E9:E83")
If IsError(myCell.Value) Then
MsgBox "Cell " & myCell.Address & " has an error, so please fix it!"
GoTo SaveCode:
End If
Next myCell

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. The code is not working and is not checking any
errors for E9:E83. It took directly to the "No Errors:" section although
there are some errors in E10 and E12 (when I tested).

Is it possible that we stick to the IF structure in my previous message? As
you know, I am checking to make sure that they input a week ending date,
their employee # and no errors in E9:E83. Then do the rest with Else.

Thanks. Have a safe and nice holiday season!

"Bernie Deitrick" wrote:

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.