If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
how do i make a cell a search cell for a spreadsheet
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? |
#3
|
|||
|
|||
how do i make a cell a search cell for a spreadsheet
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? |
#4
|
|||
|
|||
how do i make a cell a search cell for a spreadsheet
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? |
#5
|
|||
|
|||
how do i make a cell a search cell for a spreadsheet
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? . |
#6
|
|||
|
|||
excel search cell
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 |
Thread Tools | |
Display Modes | |
|
|