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
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
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
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
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
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
RE: VBA Find
Thanks,
Roger
RE: VBA Find
RE: VBA Find
RE: VBA Find
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!