Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching a spreadsheet
For work, I have a spreadsheet of all my current cases. Some cases ar listed multiple times for seperate jobs. I need to find a way t search for the case number and get the details for each listing withou knowing before hand how many listings there are for a particular case. Does anyone know how to do this? Also, how exactly do you get the Enter key to create a click event fo only one button, no matter where in the form you are? (i.e. hittin enter in a text bx generates a click on the comand button) Any help greatly appreciated. Antr -- antr ----------------------------------------------------------------------- antro's Profile: http://www.excelforum.com/member.php...fo&userid=1577 View this thread: http://www.excelforum.com/showthread.php?threadid=27293 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching a spreadsheet
http://www.bmsltd.ie/MVP/
This is a free utility that will seach a entire workbook, find and display all incidences of your search. as to your second question, i don't think you can do that. -----Original Message----- For work, I have a spreadsheet of all my current cases. Some cases are listed multiple times for seperate jobs. I need to find a way to search for the case number and get the details for each listing without knowing before hand how many listings there are for a particular case. Does anyone know how to do this? Also, how exactly do you get the Enter key to create a click event for only one button, no matter where in the form you are? (i.e. hitting enter in a text bx generates a click on the comand button) Any help greatly appreciated. Antro -- antro ---------------------------------------------------------- -------------- antro's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=15776 View this thread: http://www.excelforum.com/showthread...hreadid=272933 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching a spreadsheet
You can use AutoFilter to search out a string. I use a similar routine
to search a string, then copy the entire row when found to another sheet for a report: '/// Dim LR As Long, LC As Integer, FilterRange As Range, CopyRange As Range, DestSheet As Worksheet Dim LZ As Long '/// define destination worksheet name Set DestSheet = Worksheets("name") '/// Start Worksheets("source sheet").Activate ActiveSheet.Unprotect ActiveSheet.AutoFilterMode = False '/// Begin finding search string ActiveSheet.AutoFilterMode = False LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row LC = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column Set FilterRange = Range(Cells(1, 1), Cells(LR, LC)) FilterRange.AutoFilter Field:=10, Criteria1:="enter search criteria here" LZ = DestSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2 On Error Resume Next With FilterRange ..Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy DestSheet.Cells(LZ, 1) End With ActiveSheet.AutoFilterMode = False antro wrote: For work, I have a spreadsheet of all my current cases. Some cases are listed multiple times for seperate jobs. I need to find a way to search for the case number and get the details for each listing without knowing before hand how many listings there are for a particular case. Does anyone know how to do this? Also, how exactly do you get the Enter key to create a click event for only one button, no matter where in the form you are? (i.e. hitting enter in a text bx generates a click on the comand button) Any help greatly appreciated. Antro -- Jerry ~~~ plz remove "nospam." when replying via e-mail ~~~ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching in Excel spreadsheet | Excel Discussion (Misc queries) | |||
Formula for searching spreadsheet | Excel Worksheet Functions | |||
Searching a spreadsheet | Excel Discussion (Misc queries) | |||
Using VBA code when searching in a spreadsheet | Excel Programming | |||
Searching a spreadsheet | Excel Programming |