×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Find Macro

Find Macro

Find Macro

(OP)
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

RE: Find Macro

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

RE: Find Macro

(OP)
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

RE: Find Macro

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:

CODE

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: www.essexsteam.co.uk

RE: Find Macro

(OP)
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.

RE: Find Macro

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

good luck!
-pmover

RE: Find Macro

(OP)
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.

RE: Find Macro

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: www.essexsteam.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources