Search inside an array and return a row?

Solved/Closed
AJ - Dec 15, 2011 at 11:12 AM
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
- Dec 29, 2011 at 08:56 PM
Hello, I have a spreadsheet of data, and I'd like to create a macro where I can input a name (from within the unique names listed in column A for example) into a cell, then have the row with the corresponding name value show up in the same worksheet (the "search input would live above the array). Any help with that would be awesome! Thanks!



3 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Dec 15, 2011 at 11:24 PM
suppose your data is like this

a
s
d
f
g
h



(data starts from 11 th row)
name data1 data2
a 78 59
s 100 79
d 85 21
f 59 55
g 74 92


right click sheet tab and click view code and in the window that comes up copy this event code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rdata As Range, cfind As Range
If Target.Row > Range("a1").End(xlDown).Row Then Exit Sub
If Target.Column > 1 Then Exit Sub
Application.EnableEvents = False
Set rdata = Range("a11").CurrentRegion
'IF THE DATA STARTS OTHER THAN 11TH ROW THEN CHANGE ABOVE STATEMENT
'MsgBox rdata.Address
rdata.Cells.EntireRow.Interior.ColorIndex = xlNone
Set cfind = rdata.Find(what:=Target.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind.EntireRow.Interior.ColorIndex = 6
End If
Application.EnableEvents = True
End Sub


now open a module(click alt+F11 and control+R in the project window
highlight this file and in the vb editor click insert -module
a module will come up)
there you copy this macro


Sub undo()
Range(Range("a1"), Cells(Rows.Count, "A").End(xlUp)).EntireRow.Interior.ColorIndex = xlNone
End Sub



now go to the sheet and choose any cell in search area (that is in this sample case
A1 to A6
see what happens in the data area.
0
Venkat, that's almost right! Thanks for the help. What I would really like, though, is to write "A" in cell A1, and then have a 78 59 show up (perhaps be copied) starting in cell A2, from a very long list of data starting in A11. Highlighting the row won't help if the list of data is well over 1000 cells long. Thanks again, though, this may help me figure it out!
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Dec 16, 2011 at 08:48 PM
ok now your data is now like this

data starts from A11 and beloq. Above the data the sheet is blank
name data1 data2
a 78 59
s 100 79
d 85 21
f 59 55
g 74 92


right click sheet tab and click view code
DELETE THE OLD CODE which you have put there

now copy this event code there

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rdata As Range, cfind As Range
If Target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
Set rdata = Range("a11").CurrentRegion
'IF THE DATA STARTS OTHER THAN 11TH ROW THEN CHANGE ABOVE STATEMENT
'MsgBox rdata.Address
Set cfind = rdata.Find(what:=Target.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind.EntireRow.Copy Range("A2")
Else
MsgBox "that value is not available"
End If
Application.EnableEvents = True
End Sub



SAVE THE FILE

now type some entry in A1 repeat only in A1 (whether it is in the column A of the data below or any name not available there) and see what happens in row no. 2
0
Venkat:
This works amazingly! Thank you so much, I appreciate it! Any chance you could recommend a beginner's Macro book to help me learn the basics a bit better?
Cheers!
-AJ
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Dec 29, 2011 at 08:56 PM
there are many books
try
excel power programming with vba
by
John walkenbach

FOR YOUR VERSION OF EXCEL.
0