Insert Search Bar in Excel Spreadsheet

[Vsweeten]

I am brand new to VBA so I need a very beginner type instruction set.
I have a spreadsheet only 1 worksheet, with all of the data in it.
I want to insert a search bar to be able to search the sheet by employee name and then have that employee's row of information show up at the top of the screen (or as the 2nd row of data).
I have tried just about all of the recommended VBA and macro's and nothing gives me what I want??
The data is all in columns A through F and there will be a lot of rows by the time it is done.
Can anyone please help.???
I have the command button and have assigned different macros to it in an attempt to get what I want and I get some things but not all of it....
Please help!!!

 

[My Aswer Is This]

In 20 years of using Excel I have never heard of a your quote:
"I want to insert a search bar"

What is a search Bar??

Try this script and see if it does what you want.
A InputBox will popup asking for a name.
Put in name you want to search for. Name must be in column "A"
Row (2) will now be filled with data from row where name was found.

Sub Search_Bar()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As String
ans = InputBox("Enter Name")
    For i = 3 To Lastrow
        If Cells(i, 1).Value = ans Then Rows(2).Value = Rows(i).Value
    Next
Application.ScreenUpdating = True
End Sub

 

[Jmoz092]

In 20 years of using Excel I have never heard of a your quote:
"I want to insert a search bar"

What is a search Bar??

Try this script and see if it does what you want.
A InputBox will popup asking for a name.
Put in name you want to search for. Name must be in column "A"
Row (2) will now be filled with data from row where name was found.

Sub Search_Bar()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As String
ans = InputBox("Enter Name")
    For i = 3 To Lastrow
        If Cells(i, 1).Value = ans Then Rows(2).Value = Rows(i).Value
    Next
Application.ScreenUpdating = True
End Sub

How do you apply that code to multiple sheets? Can you run this code on sheet 1 while searching sheet 2?

 

[My Aswer Is This]

You need to explain more.

Did this script do what you wanted on the active sheet. Yes or No ?

1. Your now wanting to search multiple sheets for the same name and do what?

Copy that rows data to row(2) of the sheet where the name was found?

And what does multiple sheets mean?
Do you mean all sheets in the workbook?

 

[Jmoz092]

Sorry, I was just reading through the forums and ran across this thread that can fit into an existing project I have.

On one sheet I have 4 columns, with 4 rows to each column. Each column's 4 rows of cells is a validated list pulled from a named range from their own sheet in the workbook.
Sheet 1 B13:B17 is populated from a named range on Sheet 2
sheet 1 D13:D17 is populated from a named range on Sheet 3
sheet 1 F13:F17 is populated from a named range on Sheet 4
sheet 1 H13:H17 is populated from a named range on Sheet 5

Sheet 2 has data in columns E and F that are concatenated in column G.
G1:G464 is the named range for the validated list on sheet 1 B13:B17

Sheet 3 has data in columns A and B that are concatenated in column D
D5:D535 is the named range for the validated list on sheet 1 D13:D17

Sheet 4 has data in columns E and F that are concatenated in column G.
G1:G459 is the named range for the validated list on sheet 1 F13:F17

Sheet 5 has data in columns F and G that are concatenated in column H.
H1:H541 is the named range for the validated list on sheet 1 H13:H17

Also, Sheets 2-5 are hidden from the users.

The validated lists are very long, but serve a purpose insofar that they concatenate a number with a description. This is then selected, to suit a given scenario, into the cell. After some user feedback, it seems that waiting for the numerically ascending ordered validated list to scroll down to their desired selection is "tedious" and "time consuming". I thought a search box specific to each named range above each of the columns may be a helpful solution to the users' feedback.

As an add on inquiry, is it possible to choose or select the result of the search and then have it populated into the next empty row of the column that the search box belongs to?

Fascinating stuff, thanks for the discussion.

 

[My Aswer Is This]

I do not see how any of this has any thing to do with your original simple question. And you answered none of my questions. So I will assume your question has now been answered with all this information you have found.
Take care.

Sorry, I was just reading through the forums and ran across this thread that can fit into an existing project I have.

On one sheet I have 4 columns, with 4 rows to each column. Each column's 4 rows of cells is a validated list pulled from a named range from their own sheet in the workbook.
Sheet 1 B13:B17 is populated from a named range on Sheet 2
sheet 1 D13:D17 is populated from a named range on Sheet 3
sheet 1 F13:F17 is populated from a named range on Sheet 4
sheet 1 H13:H17 is populated from a named range on Sheet 5

Sheet 2 has data in columns E and F that are concatenated in column G.
G1:G464 is the named range for the validated list on sheet 1 B13:B17

Sheet 3 has data in columns A and B that are concatenated in column D
D5:D535 is the named range for the validated list on sheet 1 D13:D17

Sheet 4 has data in columns E and F that are concatenated in column G.
G1:G459 is the named range for the validated list on sheet 1 F13:F17

Sheet 5 has data in columns F and G that are concatenated in column H.
H1:H541 is the named range for the validated list on sheet 1 H13:H17

Also, Sheets 2-5 are hidden from the users.

The validated lists are very long, but serve a purpose insofar that they concatenate a number with a description. This is then selected, to suit a given scenario, into the cell. After some user feedback, it seems that waiting for the numerically ascending ordered validated list to scroll down to their desired selection is "tedious" and "time consuming". I thought a search box specific to each named range above each of the columns may be a helpful solution to the users' feedback.

As an add on inquiry, is it possible to choose or select the result of the search and then have it populated into the next empty row of the column that the search box belongs to?

Fascinating stuff, thanks for the discussion.

 

[Jmoz092]

I do not see how any of this has any thing to do with your original simple question. And you answered none of my questions. So I will assume your question has now been answered with all this information you have found.
Take care.

Sorry, I thought the post contained an explanation of how my needs differed from the OP's needs as my search is across multiple sheets and the search column is variable through the workbook; I should have been more clear that the provided code did not work...for my needs at least. The concept seemed similar to my needs, so I inquired.

 

[My Aswer Is This]

I guess I became confused. I see now your not the original poster. The OP has never answered back. If you have a question it's best to start a new thread.

Sorry, I thought the post contained an explanation of how my needs differed from the OP's needs as my search is across multiple sheets and the search column is variable through the workbook; I should have been more clear that the provided code did not work...for my needs at least. The concept seemed similar to my needs, so I inquired.

 

[Jmoz092]

Kindly noted, thank you for the feedback.

 

[Vsweeten]

Well the macro runs, (the information is actually located in column B), but it does not bring the employee to the top row, in fact I can't see where it does anything when I put the name in the box?? It's not that I want a search bar specifically I just want the ability to type in a name or department etc... and get the information to go to the second row of the sheet.
I assigned this macro to a button. Here is what I put in:

Sub Search_Bar()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Dim ans As String
ans = InputBox("Enter Name")
For i = 3 To Lastrow
If Cells(i, 1).Value = ans Then Rows(2).Value = Rows(i).Value
Next
Application.ScreenUpdating = True
End Sub

In 20 years of using Excel I have never heard of a your quote:
"I want to insert a search bar"

What is a search Bar??

Try this script and see if it does what you want.
A InputBox will popup asking for a name.
Put in name you want to search for. Name must be in column "A"
Row (2) will now be filled with data from row where name was found.

Sub Search_Bar()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As String
ans = InputBox("Enter Name")
    For i = 3 To Lastrow
        If Cells(i, 1).Value = ans Then Rows(2).Value = Rows(i).Value
    Next
Application.ScreenUpdating = True
End Sub