Link to home
Start Free TrialLog in
Avatar of joyacv2
joyacv2Flag for Puerto Rico

asked on

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

Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Works fine for me.
What is the error?

By the way, this:

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

can be:

WS.Range("A1:J150")

Kevin
What error do you get?
Avatar of joyacv2

ASKER

error 2015
Avatar of joyacv2

ASKER

if a change .value by .text, all ok, but i don't know why?
Are any of your sheets protected or hidden?
What is WS.Name when the error occurs?
Is there anything unusual about that sheet?
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
You said it was line 11 causing the problem, not line 12 so I'm confused.
He didn't say anything about line 11. He said this line invoked an error:

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

Kevin
You're right, sorry.
Avatar of joyacv2

ASKER

but, why using .text solves the problem?
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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