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
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