PDA

View Full Version : "Excel 2002 Search Macro Help"



TDK1966
08-10-2010, 08:59 AM
Hello folks.. thanks for having me. I know enough about VBA to be dangerous. :rotlaugh: Need a little help on a search macro I am using. It's working beautifully and can't figure out a couple things.
I need to search alpha and numerical with it. (at This moment I'm searching Alpha only.
there is a bug. If you cancel the search box or send a blank search query Excel will freeze.I've attached the sample workbook and the macro code I am using is below. I really can't start over at this time or I will get my walking shoes :rotlaugh:.. I've used a lot of macro buttons to dumb it down a bit for a few of the workers we have here... can't teach an old dog new tricks. :whistle:

On worksheet "Master" is our Database. the bosses use to micro-manage.. we need to be able to search on date and Job number as well as everything else. This code is not case sensitive and if the cell has "This TRUCK is awesome" in it for example, we can search for truck and it copies the row over to the "Searched_Data" worksheet.

try and cancel the search box or exit out of it and she freezes. also send a blank search query and she freezes on you.

Please HELP :rotlaugh:


Sub Find()
Dim intS As Integer
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet

Application.ScreenUpdating = False

intS = 3
'This step assumes that you have a worksheet named
'Search Results.
Set wSht = Worksheets("Searched_Data")
strToFind = InputBox("Enter the search term you're looking for")

'Change this range to suit your own needs.
With ActiveSheet.Range("A4:L65536")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.EntireRow.copy wSht.Cells(intS, 1)
intS = intS + 1
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
End If
End With

End Sub

GTO
08-10-2010, 11:01 AM
Greetings and welcome to the forum :-)

I admit I looked rather quickly, so take these initial observations/suggestions for what they are worth...


Icarumba! You have attached a 5meg wb, with something like 21 modules. You'll notice that when someone is hawking some new confection at the market, the sample is bite-sized; not a 15 lb. "I want a heart attack by 6:00 PM" sized box. Maybe just me, but by the time I found the sub, I was bleary eyed... Okay, I'm done funnin', but I would suggest just the appropriate procedures included.
Your Sub is named Find. If nothing else, naming variables, procedures, etc, the same as Methods etc is confusing.
I do not think that it is 'freezing' per se. Rather, when you either cancel the input or click <OK> with no entered data, you are sending .Find an empty string to locate. I'm pretty sure its doing that just fine, leastwise til you run out of rows on the destination sheet.
You mention that the search is case insensitive, but do not provide the argument (arg) in .Find. I think that this will then rely upon what the last search (manual or by code) had this arg set as. You will want to include the arg.
Searching for dates is a bit funny. By recollection, if searching xlFormulas, what is entered in the InputBox would need to be like what shows in the Formula Bar, irregardless of what the cell shows due to cell (number) formatting. By example, you have 01/15/10 as the first date. If you have that cell selected, what you probably have in the formula bar is 1/15/2010. So if LookIn is xlFormulas, then you'd need to enter 1/15/2010. Conversely, if LookIn is xlValues, then it is looking for the text displayed in the cell. Hope I got that right, but the basics are that dates can be a little stinky to look for.To fix the empty string problem, not tested, but a simple
strToFind = InputBox("Enter the search term you're looking for")
If strToFind = vbNullString Then Exit Sub

might be a quick fix.

Hope that helps, and again, welcome here :-)

Mark

TDK1966
08-10-2010, 01:49 PM
LOL Thank You Mark :doh:like my grand mother always said when she popped me on the back of the head "That's what learnin' is for" :D Can't learn if I don't get popped on the back of the head LOL

Looks like the fix may just work. It stopped it from searching for an empty string but won't copy over any actual strings. I'll mess around with it and let you know.

Thanks for the warm welcome. Gonna go giver er a good try now have a good evening.

Will

TDK1966
08-16-2010, 10:41 AM
Worked great with a little tweaking... Just had a meeting with the bosses and they must be able to search Date and job number such as 09-05013 now I can not search the job numbers unless I stick the apostrophe in each sell rendering any formulas moot... correction I add the apostrophe into the first cell of the column for date and job number and works ok...

But what I found out they need to be able to search for the month of say "July" I must keep it simple for stupid and use macro buttons and copy search result to another worksheet...

So, I have a .find macro that searches for partial terms . If I'm searching for "Truck" it brings up and copies all rows (To the said worksheet) with the word truck in it. has worked well because its not case sensitive. BUT, it will not allow me to search the job number and date .

Bottom line can anyone help me with this? this is what I need to do.

1. search with partial terms. case not sensitive.
2. search Date by month and yet still have month, day and year in the cell.
3. search job number

here is the .find macro I found and am using now.

Sub Find()
Dim intS As Integer
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet

Application.ScreenUpdating = False

intS = 3
Set wSht = Worksheets("Searched_Data")
strToFind = InputBox("Enter the search term you're looking for")
If strToFind = vbNullString Then Exit Sub

With ActiveSheet.Range("A4:L65536")
Set rngC = .Find(What:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.EntireRow.copy wSht.Cells(intS, 1)
intS = intS + 1
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
End If
End With

End Sub

Thank you in advance for your help on this and dealing with me on this..
Will