finding case numbers

[em]

Is it possible to do a 'find' of a series of numbers (not within a range) eg.
case numbers? I am wanting to look up about 150 individual cases yet don't
want to have to find them individually within the 15 worksheets where they
are currently hiding.
Please help???

 

[Gary''s Student]

Try this small User Defined Function:

Public Function FindcaseNumber(r As Range) As String
Dim s As Worksheet, v As Variant, ret As String, ffirst As Boolean
ret = ""
v = r.Value
addy = Application.Caller.Address
ffirst = True
For Each s In ThisWorkbook.Sheets
For Each rr In s.UsedRange
If rr.Address <> addy Then
If rr.Value = v Then
If ffirst Then
ret = s.Name & rr.Address
ffirst = False
Else
ret = ret & ", " & s.Name & rr.Address
End If
End If
End If
Next
Next
FindcaseNumber = ret
End Function


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

 

[JCS]

Hi,

Because you do not mention what version of Excel you are using or if you
mean all 15 workisheetss are in 1 workbook I'm going to assume that you are
using Excel version 2003 and all 15 worksheets are in 1 workbook. You would:

1. Right click on any of the worksheet tabs.
2. Pick "Select All Sheets" from the drop down menu.
3. Select Edit from the menu bar.
4. Select Find and then a case number.

You should be able to find any case number you wish, but you must have all
of the worksheets selected in the workbook. If the data is in 15 separate
workbooks you will have to open each individually. Again, with the info you
provided this is my best solution. PLEASE presee Yes if this helped.

JCS