I need some help with a search routine.

I need to search through Column B (dynamic # of rows), for spreadsheets that
the tabs are named after months.

(June, July, august, etc..) Which is basically each tab except for one
called Dr. Refs. I need it to search for a string within those cells in
Column B, and keep an array of those Sheets:CellLoc's..

If I can get a search going, I'm sure I can do the rest. Excel 2000, and
Excel XP, any help appreciated.

Thanks very much. =D
-khai

A couple of different ways this could be done in Excel 2k and XP.

You can use the Find Method on the worksheet as one way as you can use this
method for exact match or not and if it is in formulaes or values.

You can use your own code to search for it to either find an exact match or
if it's within value of the cell.

Dim WS as Worksheet, FirstRow as Long, LastRow as Long, SearchVal as
Variant, I as Long
'Assuming the value to search for is in Cell B3 of Dr. Refs worksheet in
"Book1.xls" workbook.
SearchVal = Workbooks("Book1.xls").Worksheets("Dr. Refs").Range("B3").Value
For Each WS in Workbooks("Book1.xls").Worksheets
Select Case WS.Name
Case "January", "February", "March", "April", "May", "June", "July",
"August", "September", "October", "November", "December"
FirstRow =
WS.Range("B:B").Find("ColumnBHeading",WS.Range("B6 5536"),xlWhole,xlRows,xlNe
xt,True) + 1
LastRow = WS.Range("B65536").End(xlUp).Row
If LastRow = FirstRow Then
For I = FirstRow to LastRow Step 1
'Look for exact match
If ws.Range("B" & I).Value = SearchVal Then
MsgBox "Exact match found in Cell B" & I & ".",48
End If
'Look to see if SearchVal is within the value of the
cell.
If InStr(1,ws.Range("B" & I).Value,SearchVal,0) Then
MsgBox "Search Value is found within the cell of B"
& I &".",48
End If
Next I
End If
End Select
Next

Hope this is of help for your search portion of your code.
--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Khai" wrote in message
news:JWm8b.325328$cF.98479@rwcrnsc53...
I need some help with a search routine.

I need to search through Column B (dynamic # of rows), for spreadsheets
that
the tabs are named after months.

(June, July, august, etc..) Which is basically each tab except for one
called Dr. Refs. I need it to search for a string within those cells in
Column B, and keep an array of those Sheets:CellLoc's..

If I can get a search going, I'm sure I can do the rest. Excel 2000, and
Excel XP, any help appreciated.

Thanks very much. =D
-khai

You need to watch for linewrap. This will get you started. It cycles
through all sheets, except "Dr. Refs", and changes all instances of
"Hello" to a gray interior color. You can adapt it to fill an array
with Sheets:CellLoc's.

Sub FindMe()
' Highlights cells that contain "Hello"

Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht as Worksheet

strToFind = "Hello"

For Each wSht in WorkSheets

If wSht.name < "Dr. Refs" Then

wSht.Activate

With wSht.Range("B:B")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.Interior.Pattern = xlPatternGray50
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <
FirstAddress
End If
End With

End If

Next wSht

End Sub

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
On Fri, 12 Sep 2003 16:58:17 GMT, "Khai" wrote:

I need some help with a search routine.

I need to search through Column B (dynamic # of rows), for spreadsheets that
the tabs are named after months.

(June, July, august, etc..) Which is basically each tab except for one
called Dr. Refs. I need it to search for a string within those cells in
Column B, and keep an array of those Sheets:CellLoc's..

If I can get a search going, I'm sure I can do the rest. Excel 2000, and
Excel XP, any help appreciated.

Thanks very much. =D
-khai