Runtime error textbox problem - help required

[N E Body]

Hello everyone

I am entering text into a textbox then trying to run code adapted from
"Find" to locate a cell on the worksheet.

I get a runtime error 424 - Object required but as I have limited knowledge
I dont know why!

When I debug if I hover the curser over the highlighted row the correct text
from the textbox is shown but if I hover over MyFind in the next row the
value is empty.

Could anyone tell me what is required?
Here is the code I am using

Sub MyFind()
'
' MyFind Macro
' Macro recorded 19/10/2004 by Kenny
'
Dim MyFind As Range
Set MyFind = UserForm1.TextBox2.Value xxxxxxxxxxxxxxxxxx this is
the problem row
'
Cells.Find(What:=MyFind, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
End Sub

 

[Trevor Shuttleworth]

Try:

Dim MyFind as Variant
MyFind = UserForm1.TextBox2.Value
' the rest of your code

As it is a text box you can probably also use:

Dim MyFind as String which is more efficient in memory (but no big deal
here)

And I would suggest you call the subroutine something different to the
variable, for example, MyFindSub

Regards

Trevor

 

[Harald Staff]

Hi.
Data type problem (below)

Dim MyFind As Range
Set MyFind = UserForm1.TextBox2.Value xxxxxxxxxxxxxxxxxx this is
the problem row

Range is an object of one or more cells. What's in your textbox is probably
what you want to search for, which is a text, string, and not a range:

Dim MyFind As String
MyFind = UserForm1.TextBox2.Text

Cells.Find(What:=MyFind, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

(Now MyFind is a bad name. I think you may have been confusing the search
criteria and the search result, which surely is a range. Those are two
different beasts.)

HTH. Best wishes Harald

 

[N E Body]

Thanks Trevor - that did the trick!

Kenny

Try:

Dim MyFind as Variant
MyFind = UserForm1.TextBox2.Value
' the rest of your code

As it is a text box you can probably also use:

Dim MyFind as String which is more efficient in memory (but no big deal
here)

And I would suggest you call the subroutine something different to the
variable, for example, MyFindSub

Regards

Trevor