How to build a macro that does this...

Solved/Closed
DG83
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018
- Sep 18, 2012 at 10:17 AM
DG83
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018
- Sep 23, 2012 at 10:20 AM
Hello guys,

I would like to build a sheet that contains several names and infomation pertaining to each name.
The first tab would be the search interface. I would like to be able to just type in the name in that first page and I would like that name and all the info just to come up in the assigned cells. I guess it would have to incorporate vlookup in some sort of way. Any ideas please?

1 reply

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
Sep 18, 2012 at 10:40 AM
Hi DG83,

Well, you basicly answered your own question.

Make sure that on your second sheet you put your names (the ones you want to look up) in the left most column of your data matrix for VLOOKUP to work.

So build your database, try VLOOKUP and post back if you get stuck/confused/need advice etc..

Best regards,
Trowa
0
I have left the most important part out, I want a button there and by pushing it I would like to have all the info copied into cells for example A1:D1. Some easy macro might solve this?
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
Sep 20, 2012 at 09:47 AM
Hi DG83,

Do you want to keep the retrieved data?

The following macro will keep the search result:
Sub test()
Dim lRow As Integer
Dim SearchName As String
lRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

SearchName = InputBox("Please input name:")

For Each cell In Sheets("Sheet2").Range("A1:A" & lRow)
    If cell.Value = SearchName Then
        Range(cell, cell.Offset(0, 4)).Copy
        Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    End If
Next

End Sub


I'm sure you will let me know if you desire more from this macro.

Best regards,
Trowa
0
DG83
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018

Sep 20, 2012 at 10:00 AM
will try it out! thank you a whole lot so far!
0
DG83
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018

Sep 21, 2012 at 03:28 PM
I am still having a hard time with getting the desired results. To keep it the simplest. Lets say I have two names on Sheet 2: A1-Peter,A2-Peter,B1-Johnson,B2-Jackson. On Sheet 1 I would like to be able to enter in a pop up box the search criteria from column A which in this example Peter. After entering Peter I would like that macro to return both Peters and not only the first one: so on sheet 1 I should get Peter Johnson and below that Peter Jackson.
I am very much appreciating your help here. My boss will think I am this super sharp to come up with such ideas:)
Thank you
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Sep 21, 2012 at 07:05 PM
You can use filter for that. Record macro if u r looking for a macro that would do that
0