Foolproof .find

[Robin S.]

I've been struggling with this for a while.

We get price lists from vendors in Excel format. I've designed a simple
program that uses .find to find products and display their description
and price in a simple form. The user simply keys in the product no.
into a text box and the product's details are displayed on the same
form.

Unfortunately I've had a lot of trouble due to formatting issues. .find
seems to be very picky about the source data's format and haven't found
a foolproof way to format the various lists.

Can anyone point me to a webpage or perhaps provide a brief description
of what I have to do to make our price lists compatible with .find?
Either programatically or by minipulating the source worksheets.

Thanks in advance.

Regards,

Robin

 

[Dave Peterson]

The only time I've seen .Find() be picky is when I'm working with dates. But
that doesn't sound applicable to your situation.

Are you sure you're specifying all the parms that can be used with .find? If
you don't, then excel and VBA will use the parms from the previous
..find--whether it was by the user or by code.

If this doesn't help, you may want to post what kind of find fails--and what the
cell contains where you think that there should be a match.

 

[Robin S.]

Thanks for the reply, Dave.

The .find parameters I'm using are as follows:

..Find(What:=TextBox1, LookIn:=xlValues, Lookat:=xlWhole)

This works just fine with a test worksheet which I have manually
produced using the same numbers as are in the worksheet of part numbers
from our vendor (which will not work with .find). It also works on
another price list from another vendor.

The only strange thing about the disfunctional price list is that it is
formatted to be printed into a hardcopy. Meaning, there are certain
rows which are used as headings, useful when searching for numbers in a
paper book. For instace, the list for all the "sawzall blades" will
have a "Sawzall Blades" heading in the row preceeding the first listed
blade. Obviously I didn't choose this format.

An example of the worksheet:

Part no. Description Price
48-00-1033 Sawzall blade $25.00

=ISTEXT on the part number results in TRUE
=ISNUMBER results in FALSE

This is the case in both my own test worksheet (in which .find works)
and the original vendor price list (in which .find doesn't work).

I tried to right click on the worksheet tab and selecting "Move or
Copy..." to create a new worksheet, as well as manually selecting the
range I'm using and copying it into another sheet (which sometimes
works when a sheet is protected) and this doesn't allow the .find to
work either.

I was hoping there was a worksheet function (like =TEXT(A2,0) for
instace) or some other blanket solution that I could use in order to
ensure the product number will always work.

Thanks for any thoughts. I don't mind doing my own research but I've
run out of places to start.

Regards,

Robin

 

[Dave Peterson]

Could there be extra stuff (leading/trailing spaces) in the part number cell?

If there are, then xlwhole would be a problem.

(I'd still specify all the parms to the .find command, though.)

Maybe the problem is the value in the textbox???

If you just did edit|find (after selecting that column), and search for the same
thing that you would have typed into the textbox, does it work ok?

 

[LenB]

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

 

[Robin S.]

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]

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

Dave,

Your program seems to have worked exactly as required. I will study it
closely.

Thank you again for your time. It is very much appreciated.

Regards,

Robin