You only need 'what' and "lookin" in you find statement. Also initialize
myrow to 0. Myrow may be empty which is not 0.

Dim myNumber as String
Dim myWorksheet as Worksheet
Dim myRange as Range

On Error Resume Next
myrow = 0
For Each myWorksheet in ActiveWorkbook.Worksheets
myRange = Cells.Find(What:=myNumber, _
lookIn:=x1Values)

If Not myRange Is Nothing Then
myRow = myRange.Row
Else
myRow = 0
End If
Next myWorksheet

If myRow = 0 Then
MsgBox("Number not found")


"Phrank" wrote:

I have a workbook with 5 worksheets. I was trying to execute code
that will search each worksheet for a specifically entered unique
number. If it doesn't find it on the first worksheet, it moves on to
the next until it finds it, or it yields a messagae stating the number
was not found.

I've pulled the code together from a couple different posts here,
because my original attempt was yielding me the "Object variable or
with block variable not set" error message.

The code below runs, but it doesn't seem to find the number or return
the row that number is on. Any ideas? Thanks.


Dim myNumber as String
Dim myWorksheet as Worksheet
Dim myRange as Range

On Error Resume Next
For Each myWorksheet in ActiveWorkbook.Worksheets
myRange = Cells.Find(What:=myNumber, After:= _
ActiveCell, LookIn:=x1Values, _
SearchOrder:=x1ByColumns)
If Not myRange Is Nothing Then
myRow = myRange.Row
Else
myRow = 0
End If
Next myWorksheet

If myRow = 0 Then
MsgBox("Number not found")