searching a spreadsheet

[antro]

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

 

[Guest]

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.

 

[Jerry Price]

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

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