For each cells error

Hi,

I got a error in the following code in the line "if cell.value = "READINGS BY" then". Any idea?

Sub READINGS()


Dim WS As Worksheet
Dim cell As Range



For Each WS In ThisWorkbook.Sheets

For Each cell In Sheets(WS.Index).Range("A1:J150").Cells
If cell.Value = "READINGS BY" Then
MsgBox ""
Exit Sub



MsgBox WS.Index
Exit Sub
End If


Next cell

Next WS

End Sub

Open in new window

LVL 1
joyacv2Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Because the Text property ignores errors and returns the value in the cell as displayed. When you use the Value property the cell value is interpreted as it is stored internally. If the cell contains an error it will generate an error in many cases.

Kevin
0
 
Martin LissOlder than dirtCommented:
Works fine for me.
0
 
zorvek (Kevin Jones)ConsultantCommented:
What is the error?

By the way, this:

Sheets(WS.Index).Range("A1:J150")

can be:

WS.Range("A1:J150")

Kevin
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Martin LissOlder than dirtCommented:
What error do you get?
0
 
joyacv2Author Commented:
error 2015
0
 
joyacv2Author Commented:
if a change .value by .text, all ok, but i don't know why?
0
 
Martin LissOlder than dirtCommented:
Are any of your sheets protected or hidden?
What is WS.Name when the error occurs?
Is there anything unusual about that sheet?
0
 
zorvek (Kevin Jones)ConsultantCommented:
You probably have some errors in the target worksheets. Use the Text property to avoid any problems. It works in your case since you are comparing to a text string.

Kevin
0
 
Martin LissOlder than dirtCommented:
You said it was line 11 causing the problem, not line 12 so I'm confused.
0
 
zorvek (Kevin Jones)ConsultantCommented:
He didn't say anything about line 11. He said this line invoked an error:

"if cell.value = "READINGS BY" then"

Kevin
0
 
Martin LissOlder than dirtCommented:
You're right, sorry.
0
 
joyacv2Author Commented:
but, why using .text solves the problem?
0
 
zorvek (Kevin Jones)ConsultantCommented:
If you insist on using the Value property then you will have to test for errors in your code:

If Not IsError(Cell.Value) Then

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.