Code to bring up info if name is entered on a separate sheet

[kdlenort]

I want a code to run some type of a lookup which would bring up all the information, specific cells within the row of the name I type in to a sheet named "Search".<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Looking in several sheets named "??? Register" (the 3 question marks will be different letters or numbers on each Register sheet) starting in row 137.<o></o>
<o></o>
If the name does exist I want the information from each time the name is found, from columns B, C, D, E, G, H, & I to be listed on the Search sheet.<o></o>
If the name doesn't exist on the ??? Register sheets I would like a pop up to open and indicates "Name not found" or "Name doesn't exist".<o></o>
<o></o>
Is this possible??<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><o></o>

 

[kdlenort]

You are FABULOUS!!

I thank you so very much, the code works absolutly perfectly!

No ifs, ands or buts to add to it.

 

[kdlenort]

Oooops one little issue
And I said there were no ifs, and or buts

When I delete the name in cell B2 on the Search sheet the code clears the cell borders from column A to column G only in row 6 (I have borders on A6:H104) it is also leaving the Source sheet reference in cell H6.

Is there a fix to not clear the borders from A6:G6 (I have borders on A6:H104) and to clear cell H6's Source sheet reference (not the cell borders)?

This is not a huge issue but I like a spreadsheet to be uniform and with the borders missing it looks as if I forgot to add them to those specific cells.

 

[lenze]

There is nothing in the code that would clear borders and H6 should be cleared!
I'll PM you my email so you can send me your file. Maybe I can spot what's going on!!
lenze

 

[kdlenort]

Thanks lenze

I sent the sample workbook to you.

I changed a few things within the code, such as the row references to be able to add names above the cell where you insert the name you are searching for so it would automatically pop up while typing.

Thanks Much!!

 

[lenze]

WOW!!! I can't for the life of me figure out what is causing that behavior, so I just put a patch in to fix it

Set D = sh.Range("$A$36:$H" & Cells(Rows.Count, "H").End(xlUp).Row + 1)
If Target = "" Then
    D.ClearContents
    Exit Sub
End If
D.ClearContents

Instead of a list in 1 -30, why not make B32 a Data Validation List with a drop down. The code will still work!!

lenze

 

[kdlenort]

Thanks for taking a look and adding the patch.

I may add a Data Validation list to the search sheet and possibly some other bells and whistles too.

I wish I understood more about VB code perhaps someday I will.

Thank you lenze!!

 

[kdlenort]

lenze,

I am having an issue since I added the fix for the code you wrote.

If there is NOT an X in column B on the ??? Registers the Search sheet only brings up one row of info.

I've tried to fix it but as I indicated in our other messages I don't know what this particular code is doing.

 

[kdlenort]

New bug since fix was added-Code change desired!
Only one entry is showing up if “X” doesn't appear in column B on the Register sheets, an “X” is added to the register sheet only if they attended, if they didn’t attend we still need to know that they were supposed to attend.
Could the code be changed to only search one sheet, the “SS” sheet? I added the SS sheet to the workbook; I want this to be a hidden sheet if possible.
I would like the code to search only the “SS” sheet for the name that is entered to the Search sheet in cell B2 and bring up every time the name appears in column F on the SS sheet with the corresponding info on that row from columns B, C, D, E, G, H, & I (as before). The only change would be the Source Sheet reference result on the Search sheet in column H, to be changed to the value that is in column J on the SS sheet.
What you think…doable or not??

 

[kdlenort]

Re: Please HELP! Change in Search Code Needed

Instead of Cell H6 on the Search sheet bringing up the source sheet I want it to be the value from the SS sheet in column J.

Anyone out there know how I can change the code to do that??

 

[kdlenort]

Help