Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof .find
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof .find
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." wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof .find
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof .find
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? "Robin S." wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof .find
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 Dave Peterson wrote: 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? "Robin S." wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof .find
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof .find
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof .find
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 "Dave Peterson" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |