"find" button

[rharri1972]

Hello!
I have a userform with a listbox and a textbox with a command button.

I have been able to type the part number in the textbox and hit the "FIND" button and it would search through and highlight that part number in the list box.

Now for some reason it will search some but not all part numbers. The same part number i've been using for testing the sub's all of a sudden quit being found.

Here is the code:

Private Sub CommandButton3_Click()
Dim R As Range

On Error Resume Next


  Set R = Sheets("PARTS LIST").Columns("A").Find( _
    TextBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
  If R Is Nothing Then
    MsgBox "Not found"
  Else
 
  R.Parent.Select
        'Select the cell itself
    R.Select
End If

  Me.ListBox1.Value = R
     
       
   
End Sub

I don't see where anything has been changed or accidentally added or deleted. Any help?

 

[Siddharth Rout]

Couple of things

1. Avoid the unnecesssary use of On Error Resume Next. The way you have used is like telling the code to "shut up and continue".
2. If you use LookAt:=xlWhole, it means that you are looking for an exact match. Which also means that if there is a leading or trailing space then you may not get a match.
3. There is no need to select the object. Directly work with it.

Your code can also be written as (UNTESTED)

Private Sub CommandButton3_Click()
    Dim R As Range

    Set R = Sheets("PARTS LIST").Columns(1).Find(What:=TextBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
 
    If Not R Is Nothing Then
        ListBox1.Value = TextBox1.Value
    Else
        MsgBox "Not Found"
    End If
End Sub

 

[rharri1972]

Couple of things

1. Avoid the unnecesssary use of On Error Resume Next. The way you have used is like telling the code to "shut up and continue".
2. If you use LookAt:=xlWhole, it means that you are looking for an exact match. Which also means that if there is a leading or trailing space then you may not get a match.
3. There is no need to select the object. Directly work with it.

Your code can also be written as (UNTESTED)

Private Sub CommandButton3_Click()
    Dim R As Range

    Set R = Sheets("PARTS LIST").Columns(1).Find(What:=TextBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
 
    If Not R Is Nothing Then
        ListBox1.Value = TextBox1.Value
    Else
        MsgBox "Not Found"
    End If
End Sub

Sid.... it keeps coming back with 'not found'. It's in the worksheet

 

[rharri1972]

Couple of things

1. Avoid the unnecesssary use of On Error Resume Next. The way you have used is like telling the code to "shut up and continue".
2. If you use LookAt:=xlWhole, it means that you are looking for an exact match. Which also means that if there is a leading or trailing space then you may not get a match.
3. There is no need to select the object. Directly work with it.

Your code can also be written as (UNTESTED)

Private Sub CommandButton3_Click()
    Dim R As Range

    Set R = Sheets("PARTS LIST").Columns(1).Find(What:=TextBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
 
    If Not R Is Nothing Then
        ListBox1.Value = TextBox1.Value
    Else
        MsgBox "Not Found"
    End If
End Sub

Sid...is it possible to add space between the ROWS of a listbox? i see you can set each column width but am wondering if the same can happen for the Rows?

 

[Siddharth Rout]

Sid.... it keeps coming back with 'not found'. It's in the worksheet

Can you share the image of what you are trying to find? Both as typed in the Textbox and as seen in the worksheet? Also can you test something for me? Can you replace

LookAt:=xlWhole

with

LookAt:=xlPart

Does it find the match now?

 

[Siddharth Rout]

Sid...is it possible to add space between the ROWS of a listbox? i see you can set each column width but am wondering if the same can happen for the Rows?

Do you means adding blank rows between two rows?

 

[rharri1972]

Do you means adding blank rows between two rows?

Well so when my listbox fills the rows are just kind of bunched up and crowded so I was just trying to add more space. I had someone tell me that it can't be done that it just resizes itself depending on the size font that I use.

 

[rharri1972]

Can you share the image of what you are trying to find? Both as typed in the Textbox and as seen in the worksheet? Also can you test something for me? Can you replace

LookAt:=xlWhole

with

LookAt:=xlPart

Does it find the match now?

yes sir! This works great. Just so I can learn...what is the difference between xl whole and xl part? It sounds like it's only check part of it rather than ALL of it(whole)

 

[rharri1972]

Can you share the image of what you are trying to find? Both as typed in the Textbox and as seen in the worksheet? Also can you test something for me? Can you replace

LookAt:=xlWhole

with

LookAt:=xlPart

Does it find the match now?

Sid...I am taking a shot at adding this from one drive. I took the time to try and learn how. If it shows up...you can see the worksheet in the background and the user form on top. I have it to where I can click on a line in the listbox and it automatically go to that value in the worksheet as you see here. IF i can get the textbox to where I can start typing and it start to find with each character typed so that the part number ultimately "highlights" then I wont have a need for the "GO" button.

Can you share the image of what you are trying to find? Both as typed in the Textbox and as seen in the worksheet? Also can you test something for me? Can you replace

LookAt:=xlWhole

with

LookAt:=xlPart

Does it find the match now?

 

[Siddharth Rout]

Well so when my listbox fills the rows are just kind of bunched up and crowded so I was just trying to add more space. I had someone tell me that it can't be done that it just resizes itself depending on the size font that I use.

Yes, by default there is no way you can add that padding. If there is a way using APIs, then I have not tried it yet. Having said that you can add a blank like between items using

Listbox1.Additem " "

what is the difference between xl whole and xl part?

When you use LookAt:=xlWhole, it means Excel will match the entire cell contents. When you use LookAt:=xlPart, it means Excel will match only some part of the entire cell content.

When you use LookAt:=xlWhole, "Search Term: Apple" will give False for "Apple " as there is a space after apple.
When you use LookAt:=xlPart, "Search Term: Apple" will give True for "Apple " as it matches some part of the entire cell content.

LookAt:=xlPart should be avoided if there are chances for a false positive. For example, If you want to search for "Apple" and the cells have values like "Apple" , "Custard Apple", "Green Apple" etc.