×
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

VBA Find

VBA Find

VBA Find

(OP)
I need a quick macro to do the following:

File 1
a  b  c1
      c2

File 2
c1  d  e
c2  d2  e2
c3  d3 e3

I need a macro to replace c1 in File 1 with "c1 d e" from File 2.  I was thinking to take c1 File 1 and use it to search File 2 till it matches (or skip if it doesn't) and go back to File 1 and replace c1 with the values in the 3 cells "c1  d  e".  There's about 7000 c's so I don't want to do this by hand anymore.

Roger

RE: VBA Find

Hello,

I have read this over and over again, and I cannot quite understand it.

Are you saying you want to replace every cell which contains "c1" with "c1 d e". Or do you want to replace every cell which contains "c1" with the contents of c1, d1 and e1 from from file 2?

e.g. does c1, d1 and e1 contain "fred", "john" and "steve" or some other data?

Hope this helps.

----------------------------------

maybe only a drafter
but the best user at this company!

RE: VBA Find

(OP)
File 1

Part Number,  Discription,  Model
12345          Cover        abc

File 2
Model    Platform     Customer
abc       747          JAL

Desired result is:
File 1
12345  Cover   abc   747   JAL

Sorry for the confusion.  Is this better?

RE: VBA Find

Hello,

This code works for the given example, but I just realised that there are many different Model names in C1 aren't there?

Sub Macro3()
Range("c1").Select
For MYCOUNT = 1 To Range("c65536").End(xlUp).Row
If ActiveCell = "abc" Then
    Selection.Offset(0, 1).Select
    ActiveCell = "747"
    Selection.Offset(0, 1).Select
    ActiveCell = "JAL"
    Selection.Offset(0, -2).Select
End If
    Selection.Offset(1, 0).Select
Next MYCOUNT

Am working on changing different names, please bea with me.

Hope this helps.

----------------------------------

maybe only a drafter
but the best user at this company!

RE: VBA Find

Hello,

This may not be the best code but give it a try

Sub CHANGE()
Range("c1").Select
For MYCOUNT = 1 To Range("c65536").End(xlUp).Row
    OLDWORD = ActiveCell
    Windows("file2.xls").Activate
    Range("a1").Select
    For MYLIST = 1 To Range("a65536").End(xlUp).Row
    If ActiveCell = OLDWORD Then
        Selection.Offset(0, 1).Select
        COL_c = ActiveCell
        Selection.Offset(0, 1).Select
        COL_D = ActiveCell
    End If
    Selection.Offset(1, 0).Select
    Next MYLIST
        Windows("file1.xls").Activate
        Selection.Offset(0, 1).Select
        ActiveCell = COL_c
        Selection.Offset(0, 1).Select
        ActiveCell = COL_D
        Selection.Offset(0, -2).Select
    Selection.Offset(1, 0).Select
    Next MYCOUNT


LEt me know how you get on.

Hope this helps.

----------------------------------

maybe only a drafter
but the best user at this company!

RE: VBA Find

(OP)
It doesn't work but it's close enough that I think I can take it from here.  It finds the file2 info ok but it doesn't go back to file 1 to put it there.  I fixed that by moving the file1 ActiveCell = COL_c section back up into the IF block.  But most importantly, your method can be made to work.  Thanks.

RE: VBA Find

(OP)
You needed an Exit For and an extra file selection and it worked fine.

Thanks,

Roger

RE: VBA Find

Seems like you could do this hlookup without a macro.

RE: VBA Find

(OP)
There are 500 abc's and 7000 part numbers.  I'm not sure how to transfer the info any other way between the files.  Give me an idea of what you're thinking.  I'm always interested in alt solutions.

RE: VBA Find

Hello,

The Vlookup method woud require the File2 information to be sorted in order, the formula would be something like this:

in D1 enter this:

=VLOOKUP(C1,'C:\[file2.xls]Sheet1'!$C$1:$E$10,2)
and in E1 enter this:

=VLOOKUP(C1,'C:\[file2.xls]Sheet1'!$C$1:$E$10,3)

where C1:E10 needs to be changed to the full range.

Don't forget that sort though.

Just remember that the data will be active so you may want to do a COPY and PASTE SPECIAL with VALUES only if you don't want to run the risk of the data messing up.



Hope this helps.

----------------------------------

maybe only a drafter
but the best user at this company!

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