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

  • Thread starter Thread starter tarone
  • Start date Start date
T

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
 
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
 
Back
Top