how do i make a cell a search cell for a spreadsheet

How do I make a cell a search/find cell for the spreadsheet.

I want to avoid having to do ctrl+f and then put it in. I know it seems
small but this has to be done hundreds of times a day and deleting just this
step would be nice. So all data is in column 1. I want Cell A1 to be the
search/find cell for the rest of the spreadsheet. Is there a way to do this
so typing something up in cell A1 would bring up that data in the spreadsheet
as if I were doing ctrl+f?

Put the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Range, r As Range, s As String
Set t = Target
Set r = Range("A1")
If Intersect(t, r) Is Nothing Then Exit Sub
s = r.Value
Cells.Find(What:=s, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

then just typing something in A1 will cause it to be found.

Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

--
Gary''s Student - gsnu200908


"adam" wrote:

How do I make a cell a search/find cell for the spreadsheet.

I want to avoid having to do ctrl+f and then put it in. I know it seems
small but this has to be done hundreds of times a day and deleting just this
step would be nice. So all data is in column 1. I want Cell A1 to be the
search/find cell for the rest of the spreadsheet. Is there a way to do this
so typing something up in cell A1 would bring up that data in the spreadsheet
as if I were doing ctrl+f?

Only through VBA AFAIK

But it takes no longer to type into the find box than it does to type into a
cell.

What would you want to do with the results of your search?

This macro colors the found cells as gray.

Sub findthings()
whatval = ActiveSheet.Range("A1").Value
With Worksheets(1).UsedRange
Set c = .Find(whatval, LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address FirstAddress
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Thu, 22 Oct 2009 12:11:03 -0700, adam
wrote:

How do I make a cell a search/find cell for the spreadsheet.

I want to avoid having to do ctrl+f and then put it in. I know it seems
small but this has to be done hundreds of times a day and deleting just this
step would be nice. So all data is in column 1. I want Cell A1 to be the
search/find cell for the rest of the spreadsheet. Is there a way to do this
so typing something up in cell A1 would bring up that data in the spreadsheet
as if I were doing ctrl+f?

It is coming up with an error. Any suggestions?

"Gary''s Student" wrote:

Put the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Range, r As Range, s As String
Set t = Target
Set r = Range("A1")
If Intersect(t, r) Is Nothing Then Exit Sub
s = r.Value
Cells.Find(What:=s, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

then just typing something in A1 will cause it to be found.

Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

--
Gary''s Student - gsnu200908


"adam" wrote:

How do I make a cell a search/find cell for the spreadsheet.

I want to avoid having to do ctrl+f and then put it in. I know it seems
small but this has to be done hundreds of times a day and deleting just this
step would be nice. So all data is in column 1. I want Cell A1 to be the
search/find cell for the rest of the spreadsheet. Is there a way to do this
so typing something up in cell A1 would bring up that data in the spreadsheet
as if I were doing ctrl+f?

I need it to scroll down to the found result so I can then do a screen print.
it seems small but I have to click outside of the find area to do the screen
print and then click back into the find area to do the search. it would be
nice to just type in one field and do the screen print without clicking back
and forth.

"Gord Dibben" wrote:

Only through VBA AFAIK

But it takes no longer to type into the find box than it does to type into a
cell.

What would you want to do with the results of your search?

This macro colors the found cells as gray.

Sub findthings()
whatval = ActiveSheet.Range("A1").Value
With Worksheets(1).UsedRange
Set c = .Find(whatval, LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address FirstAddress
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Thu, 22 Oct 2009 12:11:03 -0700, adam
wrote:

How do I make a cell a search/find cell for the spreadsheet.

I want to avoid having to do ctrl+f and then put it in. I know it seems
small but this has to be done hundreds of times a day and deleting just this
step would be nice. So all data is in column 1. I want Cell A1 to be the
search/find cell for the rest of the spreadsheet. Is there a way to do this
so typing something up in cell A1 would bring up that data in the spreadsheet
as if I were doing ctrl+f?

.

I'm trying to develop an excel sheet which enables entry of a numeric product code, say '1134' into say cell 'A1' and then references that entry into a separate worksheet which has a list of product codes from say 1000-1234. The second worksheet has the product codes listed numerically in column A and then has say 6 rows of data extend from each code.

Ideally the macro would enable a product code to be entered into the first sheet and would then automatically enter the row data from the second sheet.

Is this possible?



Gary''s Student wrote:

Put the following event macro in the worksheet code area:Private Sub
22-Oct-09

Put the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Range, r As Range, s As String
Set t = Target
Set r = Range("A1")
If Intersect(t, r) Is Nothing Then Exit Sub
s = r.Value
Cells.Find(What:=s, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

then just typing something in A1 will cause it to be found.

Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

--
Gary''s Student - gsnu200908


"adam" wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
ASP.NET Forum Control, SQLite DB and Custom Identity
http://www.eggheadcafe.com/tutorials...ontrol-sq.aspx