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

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

.

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 ~~~