Code for modify a column

  • Thread starter Thread starter CCC
  • Start date Start date
C

CCC

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