find function
Posted by Jeff Steiner on January 17, 2002 6:31 AM
Hi,
I need to search about 20 excel files for values(example, 12569mok5) that I have in one worksheet. If a value can be found in an excel file then I need to know where. Can someone tell what function I need to use and how I might write it. Thank you, Jeff
Posted by Tom Urtis on January 17, 2002 6:54 AM
This might be overkill because it has the ability to seach more than one sheet, but see if it helps serves youyr purpose.
One thing about this code, if you do need it to search more than one sheet in the workbook, then start your search on Sheet1 as designated per the sheet object code that you can see in the VBE Project Explorer. This is because the search starts from the lowest number sheet and works its way up to and through the highest number sheet.
Thanks go to a number of people on this board who contributed pieces of code to this macro.
Sub SearchFind()
Dim searchValue
Dim counter As Integer, sheetCount As Integer
Dim startSheet, startCell
Application.ScreenUpdating = False
On Error Resume Next
startCell = ActiveCell.Address
startSheet = ActiveSheet.Name
searchValue = Application.InputBox("Type in what you want to search for:", Title:="''Search for'' data entry box", Type:=2)
If searchValue = "" Then Exit Sub
If IsError(CDbl(searchValue)) = False Then searchValue = CDbl(searchValue)
sheetCount = ActiveWorkbook.Sheets.Count
counter = 1
Do Until counter > sheetCount
Sheets(counter).Activate
Cells.Find(What:=searchValue, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Activate
If ActiveCell.Value = searchValue Then Exit Do
counter = counter + 1
Loop
If ActiveCell.Value <> searchValue Then
MsgBox "The value " & Chr(34) & searchValue & Chr(34) & " was not found.", vbExclamation + vbOKOnly, "Data not found!"
Application.Goto Reference:=Worksheets(startSheet).Range(startCell)
Application.ScreenUpdating = True
Exit Sub
End If
HTH
Tom Urtis
