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

[adam]

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?

 

[Gary''s Student]

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

 

[Gord Dibben]

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

 

[adam]

It is coming up with an error. Any suggestions?

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

 

[adam]

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.

 

[john critchley]

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 arearivate 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 Strin
Set t = Targe
Set r = Range("A1"
If Intersect(t, r) Is Nothing Then Exit Su
s = r.Valu
Cells.Find(What:=s, After:=ActiveCell, LookIn:=xlValues, LookAt:=
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext
MatchCase:=False
, SearchFormat:=False).Activat
End Su

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 windo
2. select View Code - this brings up a VBE windo
3. paste the stuff in and close the VBE windo

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

To remove the macro

1. bring up the VBE windows as abov
2. clear the code ou
3. close the VBE windo

To learn more about macros in general, see

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

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

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

-
Gary''s Student - gsnu20090

:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
ASP.NET Forum Control, SQLite DB and Custom Identity
http://www.eggheadcafe.com/tutorial...e35-1c42f5459c21/aspnet-forum-control-sq.aspx