Searching tables and displaying results

[wonderdunder]

I have a table in sheet1 with columns A to K.

Column A is a unique ID number (lets call it ID_No), the rest is information linked to it.
Column C is a job number which is not unique.

What I want to do is as follows:

1. Search for and display unique ID. I want a user form (which will only contain one textbox - textbox1) for the user to enter the ID_No they want to search for. I then want Sheet2 (which has the same column headers as sheet1) to display all the data in that row.

2. Similar to the above but with Job-No which is not unique. This time, I want to search and display (as before) all the job numbers that exactly match the search data entered and to display the rows which contain that job number.

I need this to be done by VBA - not by using filters or any other method.

Help or link to solution appreciated.

[6StringJazzer]

What is your experience level with VBA? What have you done so far to develop a solution?

[wonderdunder]

Hi there.

Any attempts I've made have been by using spreadsheets to input search criteria. I've tried macro recording, but I don't seem to get anywhere with that. I've also tried a for loop which didn't work either (I think it's my syntax). I thought once I can do it with a spreadsheet then I can just convert to a userform, but apparenyly not!

My VBA level is. well, muddle along and pray!

[wonderdunder]

I've found this code:

Please Login or Register  to view this content.

Some questions:

1. I need the range to be a named range - I take it that is simple Range("rangename")?
2. At Find "(What:=" - what do replace "1234" with if it is to be textbox1 of userform1?
3. Rather than copy the range, how do I filter (using VBA) to show just that row?

Any help appreciated.

[davesexcel]

Hello,
Select the range you want to filter, goto Data>autofilter
Use the arrows to select what to filter for.

[wonderdunder]

Thanks for the response, but I would like this to be done by using VBA.

I have come across this code:

Please Login or Register  to view this content.

But when I use it to search for "Job11" (Which is contained in A2 and A4) It filters two rows that DONT contain that information! However if it is numerical (so just 11) then it filter properly - but what if I want to filter text? Will this still keep happening?

What would be ideal is to adapt the code so that the range it is searching (A1:A1) reads as a named range and for criteria to be what is entered into a textbox (textbox1 of userform1).

[davesexcel]

You should have had errors when you ran that code.

Try.

Please Login or Register  to view this content.

You can use a variable as well

[wonderdunder]

How do I do it as a range? Preferable data entered into a textbox contained in a userform?