PDA

View Full Version : [SOLVED] update a table between two Excels files with a condition



activation01
12-08-2017, 11:24 AM
The request consists of updating a value between two tables (report 1 on the Excel file1.xls file and the report 2 table on the Excel file2.xls file), with a condition of taking the key (Column A / Column B) of the 1st file1.xls and change the value on the table the 2nd report2 which contains the same key (Column A / Column B) with the value of column C of the first report ,

Please note: note that the two tables are separate, two Excel files (rapport1.xls & rapport2.xls)
In piece joint the excel Templates.

File1.xlsx
rapport1
X type value
X1 A 20
X2 C 10
X3 A 2
X3 B 0
X1 D 5

File2.xlsx
rapport 2
X type update value
X1 A 0
X2 C 10
X3 A 1
X4 B -
X4 D -

thank's for your help & support

SamT
12-08-2017, 12:15 PM
two Excel files ...In piece joint the excel Templates.
Hunh?

offthelip
12-08-2017, 03:59 PM
try this it might get you started:

Sub movedata()Workbooks("Fichier1.xlsm").Activate
With Worksheets("rapport1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(.Cells(1, 1), .Cells(lastrow, 3))
End With


Workbooks("Fichier2.xlsx").Activate
With Worksheets("rapport2")
lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
inarr2 = Range(.Cells(1, 1), .Cells(lastrow2, 2))
outarr = Range(.Cells(3, 3), .Cells(lastrow2, 3))

For i = 3 To lastrow
For j = 3 To lastrow2

If inarr(i, 1) = inarr2(j, 1) And inarr(i, 2) = inarr2(j, 2) Then
outarr(j - 2, 1) = inarr(i, 3)
Exit For
End If
Next j
Next i
Range(.Cells(3, 3), .Cells(lastrow2, 3)) = outarr


End With

End Sub




Note I put the code in Fichier1 which means I had to change the name to a macro excel type ".xlsm"

activation01
12-11-2017, 10:06 AM
thank's for the quick ansewer , the script is work perfectly ,