search Workbook Macro

[BrendanDixon]

Hi All,

I am trying to add a search function to my excel document that I am using as a phone book. I have an index sheet where I would like to have the search button and then multiple sheets with the contacts in ie. "A", "B", "C" I would like to press the button and a search box pops up where I insert the text. then click search and it will go to the value. I would also like to be able to search for multiple instances with a next button or so.

This is all I have been able to find. PS I would like something that is not case sensitive.

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

 

[JackDanIce]

To make it non case-sensative, change this line:

datatoFind = InputBox("Please enter the value to search for")

To:

datatoFind = UCase(InputBox("Please enter the value to search for"))

 

[BrendanDixon]

Hi JackDanIce'

I see what you did I had to do the same thing for

If UCase(ActiveCell.Value) <> datatoFind Then

because if the case was different is would find the word but then it would also come up with a cannot find value msg.

But I see my code is not working correctly because if I have the same word on two different sheets it seems to skip the first word.

I would also like to be able to maybe still have the userform open and be able to click next to find the next instance of a word. Do you know how to do this?

 

[JoeMo]

Have you considered using Excel's 'Find' Dialog box for this? From any sheet in your workbook pres ctrl + F to open the dialog box. Click on Options and from the drop down 'Within' menu select 'Workbook', then set the other parameters to suit your search. The Find Next button will enable you to find each instance in the workbook one by one.

 

[BrendanDixon]

I am happy with this idea but how would I be able to use a macro on a button to launch the window and to automatically set to search all sheets