Search inside an array and return a row?
Solved/Closed
AJ
-
Dec 15, 2011 at 11:12 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Dec 29, 2011 at 08:56 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Dec 29, 2011 at 08:56 PM
Related:
- Search inside an array and return a row?
- Saints row 2 cheats - Guide
- Yahoo search history - Guide
- How to search for a word on a page - Guide
- Safe search settings - Guide
- Google.us search - Guide
3 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 15, 2011 at 11:24 PM
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
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
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.
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
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 16, 2011 at 08:48 PM
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
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
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
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 29, 2011 at 08:56 PM
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.
try
excel power programming with vba
by
John walkenbach
FOR YOUR VERSION OF EXCEL.
Dec 16, 2011 at 02:41 PM