search macro or VBA

[luke w]

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

 

[Barrie Davidson]

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]

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.

 

[ROBINSYN]

do you have an example you could email. I am trying to figure this out too, or something close.

syndee@sympatico.ca

 

[luke w]

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]

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