help with macro to synch values in 2 documents based on label in a specific column
Posted by CMorrigu on October 31, 2001 9:17 AM
Greetings all,
I'm looking to adaptively synch data from two documents based on labels in a certain column.
Doc 1:
blah, synch1, 123
blah2, synch2, 456
, synch3, 789
Doc 2:
synch1, 234
synch3, 890
synch2, 567
and the result would be that the next free column in doc1 would have the correct values from doc2:
blah, synch1, 123, 234
blah2, synch2, 456, 567
, synch3, 789, 890
I think I've already got the pre/post-processing complete, it's just being able to match the values... either source or destination doc could change slightly each time I'd want to run this, and I'd rather not have to hard-code everything.
An additional piece might be the scenario where the labels in doc1 and doc2 might not match exactly, or two separate labels from doc2 need to be combined to one in doc1:
Doc 1:
blah, synch1, 123
blah2, synch2, 456
, synch3, 789
Doc 2:
synch1, 234
synch3, 890
synch2, 567
synch2a, 102
with the results being:
blah, synch1, 123, 234
blah2, synch2, 456, 669
, synch3, 789, 890
These can be hard-coded exceptions, but the main point is to hard-code the least amount possible.
Thanks in advance for any help on this one. I apologize for having to post, but I've been searching all over for the past few hours trying to find anthing similar, with no luck.Shadow Source
Posted by CMorrigu on November 01, 2001 12:43 PM
Well, after a couple more hours research and trail and error, I found it. I figured I might as well just post it here in case anyone else is looking for a solution.
Windows(rep_file).Activate ' switch to report file
LastCol = ActiveCell.SpecialCells(xlLastCell).Column ' find last used column
LastCol = LastCol + 1 ' inc column for next available one
'LastColumn = Range("IV1").End(xlLeft).Column
'LastRow = Range("A65000").End(xlUp).Row
Windows(src_file).Activate ' switch to source file
' Range("A2").Select ' top of file
' Label = ActiveCell.Value ' get first search value
' start loop
Do While src_line < 2001 ' response line < eof? *** change
src_line = src_line + 1 ' inc src line
Windows(src_file).Activate ' set source file
Range("A" + Right(Str(src_line), Len(Str(src_line)) - 1)).Select ' select a name to find
If Selection.Text = "" Then Exit Do ' exit loop if cell is blank
Label = ActiveCell.Value ' get first search value
Windows(rep_file).Activate ' switch to source file
Cells.Find(What:=Label, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate ' search for Label
a_line = ActiveCell.Row ' set a_line to found row
Windows(src_file).Activate ' switch to source file
Range("B" + Right(Str(src_line), Len(Str(src_line)) - 1)).Select ' select value
Selection.Copy ' copy value
Windows(rep_file).Activate ' switch to report file
Range(Chr$(LastCol + 64) + Right(Str(a_line), Len(Str(a_line)) - 1)).Select ' set paste cell
ActiveSheet.Paste ' paste value
Loop
'end loop
Shadow Source
