Find Macro

[rnordquest]

Data Sheet contains generic model designations:
AA-A
AA-B
AA-D
AA-BA

How do I get a macro to find "AA-B" and "AA-BA" from my data sheet using values like AA-B1 or AA-BA2C?
=====
Model = "AA-B1"

I tried:
With ActiveSheet.Range("A3:A500")
Set c = .Find(Model, LookIn:=xlValues, LookAt:=xlPart)

and:
For Each c In [A3:A500]
If c Like Model Then

 

[m777182]

Try to use the function
InStr([start, ]your cell, "AA-B",compare]) and
InStr([start, ]your cell, "AA-BA",compare])
m777182

 

[rnordquest]

Thanks,

I'll give that a try. I'll need to use a variable for "AA-B" because I have several hundred. Will InStr work that way. I may just can the macro and use vLookup.

Roger

 

[johnwm]

A simple method (here searching first 16 rows of column 1)
On a sheet with a Command button, a ListBox and a TextBox, add this code to the Sheet code page:

Private Sub CommandButton1_Click()
ListBox1.Clear
For a = 1 To 16
If Cells(a, 1).Value Like TextBox1.Text Then ListBox1.AddItem (Cells(a, 1).Value)
Next a
End Sub

To find all cells with AA at the start type AA* in the TextBox and click the commandbutton

To find B in position 2, use ?B*

To find all '2 character only' strings use ??

? replaces a single character and * replaces all remaining characters

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[rnordquest]

I like that. It's not what I need but I like it none the less. I'm using this more like a database. Once I find the item I need to return it and 4 more pieces of data on the same row.

 

[pmover]

a thought . . .
will the built-in autofilter feature/capability suffice for your needs?

good luck!
-pmover

 

[rnordquest]

What I really will be doing is to block copy a bunch of these items in and then block copy the results back out. If I have to do one at a time I might as well look it up by hand.

Thanks for the suggestion though.

 

[johnwm]

You can return the other data from the same row easily;

If Cells(a, 1).Value Like TextBox1.Text Then ListBox1.AddItem (Cells(a, 1).Value) & vbTab & (Cells(a, 2).Value)

Of course you can return the items to stuff other than a listbox!

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk