Works like a charm! Thanks so much!

Frank

On Sun, 02 Sep 2007 09:45:14 -0500, Dave Peterson
wrote:

One mo

Option Explicit
Sub testme01()

Dim myNumber As String
Dim myWorksheet As Worksheet
Dim myRange As Range
Dim myRow As Long

myNumber = "1234"

myRow = 0
For Each myWorksheet In ActiveWorkbook.Worksheets
With myWorksheet.UsedRange
Set myRange = .Cells.Find(What:=myNumber, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlnext, _
MatchCase:=false)
If myRange Is Nothing Then
'keep looking
Else
myRow = myRange.Row
'stop looking
Exit For
End If
End With
Next myWorksheet

If myRow = 0 Then
MsgBox "Number not found"
Else
MsgBox myRow
'wanna go there, too?
Application.Goto myRange
End If

End Sub

ps. Watch your spelling/typing. It's xlvalues (ex-ell-values), not x1values
(ex-one-values) and it's xlbycolumns (ex-ell-bycolumns), not x1bycolumns
(ex-one-bycolumns).





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")