VBA Find

[rnordquest]

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

 

[onlyadrafter]

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!

 

[rnordquest]

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?

 

[onlyadrafter]

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&quot.Select
For MYCOUNT = 1 To Range("c65536&quot.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!

 

[onlyadrafter]

Hello,

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

Sub CHANGE()
Range("c1&quot.Select
For MYCOUNT = 1 To Range("c65536&quot.End(xlUp).Row
OLDWORD = ActiveCell
Windows("file2.xls&quot.Activate
Range("a1&quot.Select
For MYLIST = 1 To Range("a65536&quot.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&quot.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!

 

[rnordquest]

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.

 

[rnordquest]

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

Thanks,

Roger

 

[MintJulep]

Seems like you could do this hlookup without a macro.

 

[rnordquest]

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.

 

[onlyadrafter]

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!