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?
 
zorvek (Kevin Jones)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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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.