Search inside an array and return a row?Solved/Closed

Question

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!



Configuration: iPhone / Safari 7534.48.3

venkat1926 · Answer

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.

venkat1926 · Answer

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

venkat1926 · Answer

there are many books
try 
excel power programming with vba
by 
John walkenbach 

FOR YOUR VERSION OF EXCEL.

Search inside an array and return a row?

3 responses

Similar discussions

Subscribe To Our Newsletter!