I made a small user form (a textbox and a commandbutton) and had this code
behind it:

Option Explicit
Private Sub CommandButton1_Click()
Dim wks As Worksheet
Dim FoundCell As Range

Set wks = Worksheets("Sheet1")
If Me.TextBox1.Value = "" Then
Beep
Exit Sub
End If

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.TextBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
Lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With
End With

If FoundCell Is Nothing Then
MsgBox "Not found"
Else
MsgBox "Found it: " & FoundCell.Address
End If

End Sub

It worked fine when I typed 48-00-3011 into the textbox (or even '48-00-3011).




"Robin S." wrote:

LenB wrote:
Expanding on what Dave says, I would also try copy/paste from one of the
part number cells into the text box and see if that fails. That points
you towards whether it is a difference in the text or a param in the
.find command.

Len

Len and Dave,

The product numbers actually contain an apostrophy before the number,
ie.:

'48-00-3011

But I used =RIGHT(A2,10) to remove it. Using xlPart doesn't seem to
help either. It would probably have unfortunate results anyway as some
of our lists have several thousand product numbers which can be of any
length and one part number may be a complete part of another. I.e.
123456 and 1234 could be two part numbers in the same list. I don't
have access to the file right now, but I will try xlPart again just to
make sure.

I can do Edit/Find to find part numbers, and it works. I also did
copy/paste into the form's textbox and that doesn't work.

Indeed, even when I manually type in a test product number into the
price list (including a test description and test price) .find is
unable to find the number.

In my limited understanding of Excel, I believe selecting a column and
formatting it doesn't affect the way things like .find work. I wish the
solution was so simple.

I appriciate the time everyone's taking with my silly problem.

Regards,

Robin

--

Dave Peterson