Update Master records (swap / replace old record from new record) in two file

[tarone]

hi
I want to update AGE field in file A from file b. i.e update AGE.

The number of records in file A and File B are not equal and may be
different value.


this is what i want
If there is any new name if file b add it in file a. For all other Name
which are in file a and file b, just take the new AGE from file B and
update in file A.


file a (it has 5 colomn)
Name Number Field Qty Age
John 7878 8989 234 32
Peter 7877 23 9 NA
Mike 3433 343 --
Shart 343 343
William 323 52 5

file b (it has only two colomn)

name AGE
John 34
Peter NA
Mike NA
Josh 43
ASA NA
MOM 11




any advice on it.

thanks

 

[Don Guillett]

Try this where the existing list starts in col A and the 2nd list in col F
Sub updatefile()
For Each f In Range("f2:f" & Cells(Rows.Count, "F").End(xlUp).Row)
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
With Sheets("sheet12").Range("a2:a150")
Set c = .Find(f, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, 4) = f.Offset(, 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
Else
Cells(lr, 1) = f
Cells(lr, 5) = f.Offset(, 1)
End If
End With
Next f
End Sub