Search cells code not working

[Phrank]

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

 

[Guest]

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

 

[Don Guillett]

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]

One more:

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]

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

 

[Don Guillett]

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

 

[Dave Peterson]

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]

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

 

[Phrank]

Works like a charm! Thanks so much!

Frank

One more:

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