PDA

View Full Version : Help with search macro



mattino123
03-14-2008, 04:10 AM
Hi all,
I've come up with a macro to search multiple worksheets for entered text (i'm making a phonebook for work) but at the moment it only searches for the exact text. eg. it will only find 'Taylor' if I type in 'Taylor', I want to be able to type in 'Tay' or 'taylor', etc and still find 'Taylor'. I have posted the macro below, does anybody have any suggestions?
Thank you in advance.

Sub Find_Data()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub

Davel
03-14-2008, 04:25 AM
Hi

If you change the LookAt:=xlWhole to LookAt:=xlPart this will find the string in any part of the cell.

David

coliervile
03-14-2008, 04:33 AM
I'm not sure how far along you are with youe project, but here's a telephone book that was made on VBAX.

coliervile
03-14-2008, 04:34 AM
Here's the telephone book...