Hi again,

After looking back through the posts for possible code to pull the tab
name of the worksheet on which the number was found as a variable,
I've added this code. But it doesn't work - it still returns the
worksheet as Nothing.

Dim myWorksheet As Worksheet
Dim myRange As Range
On Error Resurme Next
myStudyRow = 0
Set myFoundWorksheet = Nothing
For Each myWorksheet in Worksheets
myStudyRow = myWorksheet.Cells._
Find(What:=myStudyNumber, _
LookIn:=xlValues).Row
myFoundWorksheet = Worksheets(myWorksheet.name)
Next Worksheet
Exit Sub

There are obviously problems with this. the code
Worksheets(myWorksheet.name) does read each sheet (I can see the sheet
name when I hove my mouse over it), but it doesn't get pulled into the
variable myFoundWorksheet (it remains 'Nothing'). Also,
myWorksheet.name changes with each worksheet, even after the
StudyNumber is found. Any ideas? Thanks.

Frank


On Mon, 03 Sep 2007 00:12:36 -0400, Phrank wrote:

Thanks Joel, Don, and Dave,

Here's what worked to find the row:

Dim myWorksheet As Worksheet
Dim myRange As Range
On Error Resurme Next
myStudyRow = 0
For Each myWorksheet in Worksheets
myStudyRow =
myWorksheet.Cells.Find(What:=myStudyNumber, LookIn:=xlValues).Row
Next Worksheet
Exit Sub

This did find the Row for me. How can I tweak this so that it SELECTS
the worksheet and returns the name of the worksheet in a variable?

The end goal is to enable the user to add information to a previously
entered report number. I appreciate the help with this.

Frank

On Sun, 2 Sep 2007 08:55:43 -0500, "Don Guillett"
wrote:

You did not specify a mynumber and your search order was x1 instead of XL. 1
is not l in excel.

Sub findnum()
On Error Resume Next
For Each ws In Worksheets
Application.Goto ws.Cells.Find(What:="999999", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
Next ws
End Sub
or
Sub findnum1()
On Error Resume Next
For Each ws In Worksheets
mr = ws.Cells.Find(What:="999999", LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Row
Next ws
MsgBox mr
End Sub