Macro that can take a value entered by a User and search for it w/in spreadsheet

Posted by Rob Miller on October 24, 2000 2:48 PM

Hello,

I am admittedly a beginner here with VBA and Macros. I am trying to run a macro that will allow a user of a spreadsheet to enter a keyword in a specific cell (say B3) and then have the macro automatically pick up that keyword entered and search for it w/in the data of a specific worksheet.

I recorded a simple macro where I highlight cell B3 and then enter any given word into that cell and then run a "find" off the Edit menu. How can I take that a step further and have the macro search for the keyword the user enters and then highlight that row and/or keyword w/in the body of the spreadsheet?

Here is the sample code from the macro.. what can be done to change this to accomplish the above task?:

Sub TEST()
'
' TEST Macro
' Macro recorded 10/24/2000 by RMiller
'

Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Find What,=Range("B3").Value, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
End Sub

---------------------------------------

Perhaps there is a completely different, more efficient way of accomplishing this?

Thanks,

Rob

Posted by Ivan Moala on October 25, 2000 12:45 AM

TEST Macro Macro recorded 10/24/2000 by RMiller

Rob
To do this automatically then place this code in
the data sheets code....right click on the sheet
tab and select View code and paste this code in.
What it does is to use the worksheets change event
to check if the target cell changed is B3, if so
the run a search via a loop for the targets text.
if it finds a match then highlight it light yellow (colorindex= 36 change the code for diff color)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$B$3" Then
Application.EnableEvents = False
Do Until ActiveCell.Address = Target.Address
Cells.Find(What:=Target.Text, After:=ActiveCell).Activate
If ActiveCell.Address &LT;&GT; Target.Address Then ActiveCell.Rows.Interior.ColorIndex = 36
Loop
Application.EnableEvents = True
End If

End Sub

So when you enter text in cell B3 the code will
automatically run.

Ivan

Posted by Rob Miller on October 25, 2000 7:48 AM

TEST Macro Macro recorded 10/24/2000 by RMiller

Hello again,

Thanks for your assistance!

Well, this code is certainly in the right direction of what I am trying to do. However, I was wondering if there was a way to take your code.. have it find the record(s) that match what I enter into Cell "B3".. and either have Excel automatically scroll to that location.. or better yet, have Excel find all instances of that record and then somehow Copy that entire row(s) and paste it into a blank row right below cell "B3" somewhere (ie, if row 54 contains the record the user is searching for, then excel would take row 54, say cells A54 through F54, for example, and copy and paste the contents into row 4 or 5, etc.) Also, the highlighting probably wouldnt be necessary unless we were to scroll to that location instead.

ALSO, if this is too cumbersome, is there a simple way to just run a macro that automatically brings up the "FIND" dialog box under the Edit menu automatically? and the macro would simply stop there, and the user would take over from there and type in his/her search criteria. Thats really all I want to do, come to think of it?!

Any suggestions? Maybe I'm getting in way over my head with this, but thats the idea!

Thanks,

Rob

Posted by Ivan Moala on October 26, 2000 12:30 AM

Rob to invoke the find dialog then use this command;

Sub Find()
CommandBars.FindControl(Id:=1849).Execute
End Sub

Ivan

Posted by Rob Miller on October 26, 2000 6:33 AM

Thanks!

PERFECT!

Thanks again.. I should've just gotten to the point the first time!

Rob

Posted by rich baur on October 26, 2000 12:31 PM

Try using a code like this

Sub Auto_Open()

Count = 0
Set RangeToSearch = Range("A6:A860")
SearchValue = Application.InputBox( _
"Enter the Search Value", _
, , 10, 10, , , 1 + 2)
If SearchValue = "" Then
End If
For Each SpecificCell In RangeToSearch
If SpecificCell.Value = SearchValue Then
SpecificCell.Activate
End If

Next SpecificCell
End Sub

First you may want to change the title of auto open. This title opens the macro as soon asa the worksheet is opened. Next change the rangetoserch to where you want to search. This macro brings up an input box. Enter the text and click ok. this will find what you are looking for.

Posted by rich baur on October 26, 2000 12:31 PM

Try using a code like this

Sub Auto_Open()