search macro or VBA

luke w

Board Regular
Joined
Oct 23, 2002
Messages
53
I am trying to create an easy search function within excel 97, by which a person can enter a keyword in some box and then have a macro search a column full of rows of varying text, and then filter/copy/ and return the resulting rows to a new location based on finding that keyword within the text.

Has anyone come across this solution?





_________________
Thanks
Luke
This message was edited by luke w on 2002-10-24 16:54
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
On 2002-10-24 16:54, luke w wrote:
I am trying to create an easy search function within excel 97, by which a person can enter a keyword in some box and then have a macro search a column full of rows of varying text, and then filter/copy/ and return the resulting rows to a new location based on finding that keyword within the text.

Has anyone come across this solution?





_________________
Thanks
Luke
This message was edited by luke w on 2002-10-24 16:54

Luke, try this (and make changes as indicated):<PRE>
Dim LastRow As Long
Dim Criteria As String

'Change the column reference to what you want
LastRow = Range("A65536").End(xlUp).Row

Criteria = "=*" & InputBox("Enter Search String") & "*"

'Change the column reference to what you want
Range("A1:A" & LastRow).AutoFilter Field:=1, Criteria1:=Criteria

'Change the column reference (copy and destination) to what you want
Range("A1:A" & LastRow).SpecialCells(xlCellTypeVisible).Copy _
Destination:=Range("I1")

'Change the column reference to what you want
Range("A1:A" & LastRow).AutoFilter</PRE>

Regards,
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
A VBA macro would not seem to be required, depending on what type of "filter/copy/"ing you will be doing.

As to doing it directly on the sheet, check out VLOOKUP.

As to returning a bunch of values based on the lookup (you mentioned "return the resulting rows"), you could just stack several vlookup cells side by side.

Since I'm guessing at the particulars of your project, this may not fit at all; but I took this shot in case it does fit. Whenever I can do things withOUT macros, I normally lean to the nonmacro solution.
 

luke w

Board Regular
Joined
Oct 23, 2002
Messages
53
ROBINSYN

I used the example given to me by Barrie Davidson and it works great except for a couple of things. The first is that in the first filter range

Range("A1:A" & LastRow).AutoFilter Field:=1, Criteria1:=Criteria

I had to leave the "LastRow" off to get it to work correctly. The second problem I am having is that when I cancel out before entering a value it copies all the data to my paste cell location instead of just refreshing what I already have. I'm very new at VBA so if anyone knows how to resolve the cancel issue that would be great to know.

Hope this helps
_________________
Thanks
Luke
This message was edited by luke w on 2002-10-24 19:35
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
I'm very new at VBA so if anyone knows how to resolve the cancel issue that would be great to know.

Hope this helps
_________________
Thanks
Luke

Luke, replace this line:

Criteria = "=*" & InputBox("Enter Search String") & "*"

with these lines:

Criteria = InputBox("Enter Search String")

If Criteria = "" Then Exit Sub

Criteria = "=*" & Criteria & "*"


Regards,

Barrie Davidson
My Excel Web Page
This message was edited by Barrie Davidson on 2002-10-25 10:08
 

Forum statistics

Threads
1,144,329
Messages
5,723,733
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top