Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
update Records reference to new file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
update Records reference to new file
Will need to use a combination of
COUNTIF() & VLOOKUP() in a Macro. Ant |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
update Records reference to new file
This should do it
:::::::::::::::::::::::::::: Sub AddEntries() Set qrange = Workbooks("File A").Sheets("Sheet1").Range("A:A") Set prange = Workbooks("File B").Sheets("Sheet1").Range("A:b") Sheets("Sheet1").Range("A2").Select Do Until IsEmpty(ActiveCell) On Error Resume Next ActiveCell.Offset(0, 4).Value = _ Application.WorksheetFunction.VLookup(ActiveCell.V alue, prange, 2, 0) ActiveCell.Offset(1, 0).Select On Error GoTo 0 Loop Windows("File B").Activate Sheets("Sheet1").Range("A2").Select Do Until IsEmpty(ActiveCell) qname = ActiveCell.Value qage = ActiveCell.Offset(0, 1).Value If Application.WorksheetFunction.CountIf(qrange, qname) = 0 Then Windows("File A").Activate ActiveCell.Value = qname ActiveCell.Offset(0, 4) = qage ActiveCell.Offset(1, 0).Select Windows("File B").Activate End If ActiveCell.Offset(1, 0).Select Loop End Sub ::::::::::::::::::::::::::::::::::::::: |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
update Records reference to new file
This should do it
:::::::::::::::::::::::::::: Sub AddEntries() Set qrange = Workbooks("File A").Sheets("Sheet1").Range("A:A") Set prange = Workbooks("File B").Sheets("Sheet1").Range("A:b") Sheets("Sheet1").Range("A2").Select Do Until IsEmpty(ActiveCell) On Error Resume Next ActiveCell.Offset(0, 4).Value = _ Application.WorksheetFunction.VLookup(ActiveCell.V alue, prange, 2, 0) ActiveCell.Offset(1, 0).Select On Error GoTo 0 Loop Windows("File B").Activate Sheets("Sheet1").Range("A2").Select Do Until IsEmpty(ActiveCell) qname = ActiveCell.Value qage = ActiveCell.Offset(0, 1).Value If Application.WorksheetFunction.CountIf(qrange, qname) = 0 Then Windows("File A").Activate ActiveCell.Value = qname ActiveCell.Offset(0, 4) = qage ActiveCell.Offset(1, 0).Select Windows("File B").Activate End If ActiveCell.Offset(1, 0).Select Loop End Sub ::::::::::::::::::::::::::::::::::::::: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Form Lookup & Update Records | Excel Discussion (Misc queries) | |||
draw 999 x 8 random records from file with 8614 records | Excel Programming | |||
Update yearly records | Excel Discussion (Misc queries) | |||
How can I update a spreadsheet for a new season of records? | Excel Discussion (Misc queries) | |||
how do I update a spreadsheet without duplicating records? | Excel Programming |