Find records

sanjaylml · #1 (**permalink**) 10-24-09, 05:15

I wish to get the records filtered/highlighted based on user's input.

For example, user input following:

A B C D

5.0 3.0 6.9 12.0

Similarly I have a master table where records appearing as :-

PartNo. A B C D

D-1991 6.0-8.0 2.2-3.0 7.4-9.0 11.0-12.0
A-252 5.0-9.0 5.4-9.0 3.2-8.0 5.4-7.0

Is their any Macro or program to search all the records within the range, based on user input.

For any clarication, please revert back.

Thanking you in anticipation of positive reply.

Thanks

Sanjay Mathur

Colin Legg · #2 (**permalink**) 10-24-09, 16:44

Hi Sanjay,

It is doable with some VBA code, but I think it would be better if your master table was laid out differently. Rather than having the acceptable spread listed in a single cell as text, it would be better to use two cells which show the minimum and maximum. So, for example, the master table looking like this instead:

Code:

PartNo.	A_Min	A_Max	B_Min	B_Max	C_Min	C_Max	D_Min	D_Max
D-1991	6	8	2.2	3	7.4	9	11	12
A-252	5	9	5.4	9	3.2	8	5.4	7

This way you have actual numbers representing the limits. With this set up you can then use or automate the advanced filter to extract the PartNos which meet the criteria - note that the user input would still be the same (just four entries). I would also expect this set-up to be easier to maintain. With your current layout, you would not be able to automate the advanced filter so you would have to build a custom procedure to do it which seems to be more work than is necessary.

Hope that helps...

sanjaylml · #3 (**permalink**) 10-26-09, 04:50

Thanks for your advice & accordingly, I have changed Master data.

I have used Advance Filter to extract data but in this case (With Min. & Max.) I am unable to extract data.

Could you show me illustration to achieve this goal?

Thanks in advance

Colin Legg · #4 (**permalink**) 10-26-09, 07:19

Sure, I've attached an example to show you the general idea.

Note that columns G-->N on the Query sheet are hidden (unhide them to see the advanced filter criteria).

Hope that helps...

sanjaylml · #5 (**permalink**) 10-27-09, 07:33

Please accept my sincere thanks for your valuable support. You have solved my query.

However, I wish to add one thing that when I click Button to match the criteria, sometimes it shows only Part no. (as shown in the file you attached) and sometimes with all relevant field fields (i.e. A Min, A Max, B Min B Max ......so on). I fail to understand the reason for the same.

I have copied the data in an another excel file & without macro, it displays all fields. One more question, without being copied your Macro, is their any option available to convert into coding when I go for Advance Filter to use List Range, Criteria Range and copy to etc.

Thanks

Colin Legg · #6 (**permalink**) 10-27-09, 07:49

Hi,

Quote:

However, I wish to add one thing that when I click Button to match the criteria, sometimes it shows only Part no. (as shown in the file you attached) and sometimes with all relevant field fields (i.e. A Min, A Max, B Min B Max ......so on). I fail to understand the reason for the same.

The key to this is the CopyToRange parameter.

Code:

Sub Button1_Click()

    Worksheets("Master").Range("A:I").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Worksheets("Query").Range("G2:N3"), _
        CopyToRange:=Worksheets("Query").Range("R2"), _
        Unique:=False

End Sub

In the example I attached, I had pre-filled in R2 with the field name "PartNo". This will mean that the query will only return results from the "PartNo" field. If you leave R2 blank then the query will return results from all the fields in the table. So, when you set the worksheet up, specify the fields you want to return within the CopyToRange on the worksheet itself and you should be fine.

Quote:

One more question, without being copied your Macro, is their any option available to convert into coding when I go for Advance Filter to use List Range, Criteria Range and copy to etc.

Depends on what you mean by "convert into coding". The advanced filter CriteriaRange and CopyToRange parameters expect to be passed range object references. You can't pass (for example) string data types into these parameters and you can't create a range object for sole use within VBA, so you have to reference ranges within a worksheet. If you want to avoid using formulas within the criteria range then of course you can construct the criteria strings within VBA and add them to the cells but, either way, you must use a range. Using formulas is fine though - to make it more robust, within the button_click() procedure, you should calculate the criteria range prior to calling the Range.AdvancedFilter() method. This will ensure correct results if the end user has calculations set to manual.

Hope that helps...

Internet Services

Web Development

Digital Marketing

Consumer Tech