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

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Phrank" wrote in message
...
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")

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

--

Dave Peterson

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

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


myvar=ActiveSheet.Name
msgbox myvar


End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Phrank" wrote in message
...
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

I wouldn't continue to loop through the worksheets after I found the first
match.

Option Explicit
Sub testme01()

Dim myNumber As String
Dim myWorksheet As Worksheet
Dim myRange As Range
Dim myRow As Long
dim WksName as String

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
wksName = myworksheet.name
'stop looking
Exit For
End If
End With
Next myWorksheet

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

End Sub

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

--

Dave Peterson

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

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