Cells.Find: Why can't I pass a variable?

[Rick S.]

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

 

[FSt1]

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

 

[Dave Peterson]

I think it makes the code easier to understand if you try the .Find and then
look to see if it was successful:

Dim FoundCell as Range
dim ws as worksheet
....
for each ws in activeworkbook.worksheets
with ws
Set foundcell = .cells.find(what:=sFindMe, _
After:=.cells(.cells.count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

if foundcell is nothing then
'not found, what should happen
else
'it was found, what should happen
application.goto foundcell, scroll:=true
exit for '???
end if

end with
next ws

(Uncompiled, untested. Watch for typos.)

 

[Jim Thomlinson]

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
'====================================

 

[FSt1]

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

 

[Rick S.]

Thanks to all!
I now understand why I always see code examples Like this:

dim rng as range
set rng = Cells.Find(What:=sFindMe

I did not realize this statement is key: "if something is not set then you
need to set it"

This was a really good topic!!!

--
Regards

Rick
XP Pro
Office 2007

 

[Rick S.]

This is maddening, maddening I tell you!
At first the code worked fine, now it doesn’t? The Find Dialog box will not
open!? As long as the Find dialog box is not open it will not iterate thru
worksheets.
Code tested in native form from Jim Thomlinson's reply.

Here is my complete code: (still testing and writing)
===================Activate User Form
Sub FindIt()
SearchWorkBook.Show
End Sub
===================Do some work
Sub CommandButton1_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

Sub CommandButton2_Click()
Unload Me
End Sub
====================
Did I change something I don't see? I was adding code to report found data
and Excel crashed, since then the macro quit working as it only finds a
single instance of the search text on a single sheet. Continued clicking of
the Command1 button (OK) does nothing more than what appears to refresh the
screen.

--
Regards

Rick
XP Pro
Office 2007

 

[Dave Peterson]

Each time you click the button, excel starts the routine from scratch. So it's
always going to find that same cell as the first occurance.

I'm not sure what Find dialog box you're writing about, though.

You may want to try Jan Karel Pieterse's FlexFind to see how he approached it:
http://www.oaltd.co.uk/MVP/

ps. I'd still specify all the .find arguments. These arguments are shared
between the code and the user interface. And you may be relying on an argument
that really isn't what you expected.

 

[Rick S.]

That addin is exactly what I was attempting to do in my project! I searched
many words looking for possible examples or code to use and just did not find
FlexFind, nor did I know to search for "flex".

Thanks for the link to the addin Dave!!!
And thanks to Jan Karel Pieterse's "FlexFind" for saving my sanity!

--
Regards

Rick
XP Pro
Office 2007

 

[Peo Sjoblom]

Jan Karel's name manager is also great to use, much better than the one in
Excel 2007


--


Regards,


Peo Sjoblom

 

[Rick S.]

Yes, I am looking that one over for possible future use.

--
Regards

Rick
XP Pro
Office 2007