Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for modify a column
Hello.
I need a code for modify a column in a file based on another file. I have 2 table ,first had a column name NAME and another file with 2 columns NAME and MNEMONIQUE.I want to modify DATE1 from first table with MNEMONIQUE from the second. Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for modify a column
Assumptions:
In Book1, the first table starts in A1, the name column is column A and the Date column is column C In Book2, the second table starts in A1, the name column is column A and the Mnemonique column is column E Adjust references below to suit you needs. Dim tbl1 as Range, tbl2 as Range Dim cName1 as Range, cName2 as Range Dim cDate1 as Range, cDate2 as Range Dim rw as Long, rw1 as Long Dim res as Variant With Workbooks("Book1.xls").worksheets("Sheet1") set tbl1 = .Range("A1").CurrentRegion set cName1 = Intersect(tbl1,.Columns(1)) 'Name set cDate1 = Intersect(tbl1,.Columns(3)) 'Date End With With Workbooks("Book2.xls").worksheets("Sheet1") set tbl2 = .Range("A1").CurrentRegion set cName2 = Intersect(tbl2,.Columns(1)) set cDate2 = Intersect(tbl2,.Columns(5)) ' Mnemonique End With rw1 = cName1(1).Row rw = rw1 - 1 for each cell in cName1 rw = rw + 1 ' skip the header row if cell.Row < rw1 then res = Application.Match(cell,cName2,0) if not iserror(res) then cDate1(rw).Value = cDate2(res).Value end if end if Next Using Vlookup rather than match is an obvious alternative, but vlookup depends on Name being the leftmost column of the table (or subset of the table) and while that is likely, was not something you said. -- Regards, Tom Ogilvy CCC wrote in message ... Hello. I need a code for modify a column in a file based on another file. I have 2 table ,first had a column name NAME and another file with 2 columns NAME and MNEMONIQUE.I want to modify DATE1 from first table with MNEMONIQUE from the second. Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify code | Excel Discussion (Misc queries) | |||
modify a line code | Excel Discussion (Misc queries) | |||
Modify Code | Excel Worksheet Functions | |||
Modify Find Code | Excel Programming | |||
Need HELP to modify code (need more automation) | Excel Programming |