Search and Display only Search Results

**Atalanta** · 10-10-2011, 08:26 AM

Hi All, I have this Search UserForm, But problem is when I type example text search for "99" in Search Results in ListBox display All records,

What I would like to do is when I search for ( example text ) "99" in Search Results in ListBox I want to show only records what have "99" not all records
( not even those who do not have a text in this example the number "99" )

**6StringJazzer** · 10-10-2011, 08:53 AM

The logic here is a bit confused. Rather than rewrite your code I will give you some tips here. If you still have trouble I can go deeper and help modify the code.

In Sub cmdfind_Click, the code finds the first instance of the search string. So far so good. But then there is a Do...Loop While which does no useful work except to count how many instances are found. That loop is where you are doing the search; it is in that loop that you should be doing the work of collecting the hits. Instead, you later call FindAll, which loads all visible rows into the list box. This seems to completely ignore the fact that you want to load only those rows that match your search criteria.

You should move the logic that identifies visible rows up into cmdfind_Click and use it to define rSearch. Then you should move the logic that loads the found row into the list box up into the Do...Loop While in cmdfind_Click.

**Atalanta** · 10-10-2011, 09:29 AM

HI 6StringJazzer

I am very new to Excel/VBA. - And I do not know how to change code,

So if It is possible change and fix My code. in order to show results only records what have search criteria.
edit or delete everything that is needed to fix what I want.

Thank You...

**6StringJazzer** · 10-10-2011, 10:31 AM

Your code is somewhat involved for someone who doesn't know how to change it. Did you write it yourself?

Attached is an update. This solves the basic problem that you posted about but you really need to take a breath and look at the rest of your code again.

Here are a few pointers:

I highly recommend using

Please Login or Register  to view this content.

as your first line of code (automatic with Tools, Options, Editor, Require Variable Declaration), so that variable declarations are required. This avoids the common bug of a typo in a variable name.

When using Find, you don't need to worry about restricting the range to rows containing data. Excel handles that quite efficiently.

You never have to use "With Me". Every property or object contained within the object containing the code (such as your user form) can be referenced without a dot. That is,

Please Login or Register  to view this content.

You have several global variables declared. I can't figure out how you're using them without more analysis. I advise avoiding globals, although they have their place.

Name controls to something meaningful. I have renamed TextBox1 to textboxSearchString as an example.

**Atalanta** · 10-10-2011, 10:43 AM

Thanks 6StringJazzer now is working very well,

No I have not worked this form, I found it in this Forum.

But 6StringJazzer if you have any better search userform please give me...

Thank you very much