SEARCHING WITH EXCEL AND A MACRO
Posted by RICHARD on December 31, 2001 6:15 AM
HOW CAN I SEARCH/FIND DATA IN A TABLE BY RUNNING A MACRO?
HERE'S WHAT I WANNA DO....
HAVE A PRODUCTS TABLE THAT CAN BE SEARCHED.
I WANT A BUTTON TO BE PRESSED (SEARCH BUTTON) AND A MACRO WILL BRING UP THE FIND FUNCTION FOR A USER TO SEARCH THE PRODUCT TABLE.....
I CAN RUN TYHE MACRO TO BRING UP THE FIND BOX - BUT THEN THE MACRO WILL NOT END UNTIL A SEARCH HAS BEEN DONE...... IS THERE NO WAY i CAN HAVE A SEARCH FACILITY ON MY EXCEL PAGE?????
THANX - ANY HELP GIVEN WILL BE APPRECIATED.
Posted by Cilea on December 31, 2001 6:26 AM
please turn off your caps lock..dunno..its pretty
annoying !!!
Posted by Richard on December 31, 2001 6:45 AM
Please do not moan in my threads - I'm in desperate need of some excel help....
Posted by Tom Urtis on December 31, 2001 7:04 AM
Would this help you?
Richard,
Try this macro, I think it does what you are looking to accomplish. It is a case sensitive, whole search, so please modify the code accordingly if you want to search for a part of a word and/or non-case sensitive values.
My thanks to Joe Was and others on this board who helped compile the syntax for this code.
HTH
Tom Urtis
Sub SearchProducts()
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
Application.ScreenUpdating = True
End Sub
