jim,
you're right. I just copied his code without looking at it closely. I was
just trying to make his error go away. thanks for the correction and the
additional input.

regards
FSt1

"Jim Thomlinson" wrote:

FSt1 - You need to drop the .Activate from the end of the statement.

The find method of a range returns a range object if one is found. If one is
not found then you are going to generate an error at the .activate line. So
as FSt1 indicates you are best off to set a range object to the return value
of the Find command. Then test the object to see if it really exists.
Basically your code is trying to fry up a fish that you may not have caught.
The final issue is that you probably are not searching the sheet that you
think you are. Even though you select ws if this code is contained in a
worksheet then by default it will check itself unless explicitly told
otherwise. Try something more like this...

'================================
Sub CommandButton3_Click()
Dim ws As Worksheet
Dim sFindMe As String
Dim rngFound As Range

sFindMe = TextBox1.Value
For Each ws In ActiveWorkbook.Worksheets
Set rngFound = ws.Cells.Find(What:=sFindMe, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not rngFound Is Nothing Then
ws.Select
rngFound.Select
Exit For
End If
Next ws
End Sub
'====================================

--
HTH...

Jim Thomlinson


"FSt1" wrote:

hi
if something is not set then you need to set it.
dim rng as range
set rng = Cells.Find(What:=sFindMe, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

any time you then the "...not set" error, this is usually it.
Regards
FSt1

"Rick S." wrote:

In the code below I am not able to pass the variable "sFindMe" with out an
error, typical "object variable or with block variable not set"

If I serach for string ".0625" it crashes, if I search for string "1" it
runs through all sheets fine?

What am I doing wrong besides programming in VBA?
I just realized the code wont stop at each instance for me to view LOL

================================
Sub CommandButton3_Click()
Dim ws As Worksheet
Dim sFindMe As String
sFindMe = TextBox1.Value
For Each ws In ActiveWorkbook.Worksheets
ws.Select
Cells.Find(What:=sFindMe, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Next
End Sub
====================================
--
Regards

Rick
XP Pro
Office 2007