View original (French)

Compare two Excel files using a VBA macro.

SaidB22 Posted messages 2 Status Membre -  
ThauTheme Posted messages 1564 Status Membre -
Hello,

As a beginner in VBA, I would like to know if anyone here could help me set up a macro that would allow me to compare two columns in two separate Excel files and fill in other columns in the destination file when the condition is met (when the values in the compared cells are identical).

In summary, I would like to compare the values in column B of file A with the values in column F of file B. If the values are identical, it should fill in other columns in file B based on file A.

Thank you in advance for your help!



3 réponses

Réponse 1 / 3
ThauTheme Posted messages 1564 Status Membre 160
 
Hello Said, hello forum,

To avoid wasting time, I suggest you send us an Excel file because I doubt that PhotoChope can do that. Also, more details on the other columns to fill out would be helpful. Your problem seems relatively simple to solve with the right file.
There are plenty of file hosting sites to send a file. For example: https://www.cjoint.com/

--
See you later,
ThauTheme
0
Réponse 2 / 3
SaidB22 Posted messages 2 Status Membre
 
Hello @ThauTheme,

Thank you for your response.

Indeed, I would like to compare two Excel files A and B and perform some manipulations when the condition is met or not:

1 - We compare column B of file A with column F of file B, if the cells are identical:

--> We fill the green columns of file B with the corresponding data from the green columns of file A.

Another additional manipulation:

- If we find that there is data in column B of file A but not in column F of file B, we add the row with the corresponding data from the green columns of file A.

Here is the link to the files in question: https://www.cjoint.com/c/LBpkdSUlJrz
0
Réponse 3 / 3
ThauTheme Posted messages 1564 Status Membre 160
 
Hello Said, hello forum,

If your two files are open, the code below should be placed in file A (which will consequently become File A.xlsm) : 

Sub Macro1() Dim CA As Workbook 'declares the variable CA (Workbook A) Dim OA As Worksheet 'declares the variable OA (Sheet A) Dim CB As Workbook 'declares the variable CB (Workbook B) Dim OB As Worksheet 'declares the variable OB (Sheet B) Dim TVA As Variant 'declares the variable TVA (Table of Values A) Dim TVB As Variant 'declares the variable TVB (Table of Values B) Dim I As Integer 'declares the variable I (Increment) Dim J As Integer 'declares the variable J (Increment) Dim TEST As Boolean 'declares the variable TEST Dim PLV As Integer 'declares the variable PL (First Empty Line) Set CA = ThisWorkbook 'defines the workbook CA Set OA = CA.Worksheets("PUBLISHING TEAM Facturation 2") 'defines the sheet OA Set CB = Workbooks("File B.xlsx") 'defines the workbook CB Set OB = CB.Worksheets("Sheet1") 'defines the sheet OB TVA = OA.Range("A1").CurrentRegion 'defines the table of values TVA TVB = OB.Range("A1").CurrentRegion 'defines the table of values TVB For I = 2 To UBound(TVA, 1) 'loop 1: on all lines I of the table of values TVA (starting from the second) TEST = False 'sets the variable TEST For J = 2 To UBound(TVB, 1) 'loop 2: on all lines J of the table of values TVB (starting from the second) 'condition: if the text after the dash of the data row I column 2 of TVA is equal to the value row J column 6 of TVB (converted to text) If Split(TVA(I, 2), "-")(1) = CStr(TVB(J, 6)) Then OB.Cells(J, 9).Value = TVA(I, 5) 'retrieves in cell row J column 9 of sheet OB the value of data row I column 5 of TVA OB.Cells(J, 11).Value = TVA(I, 6) 'retrieves in cell row J column 11 of sheet OB the value of data row I column 6 of TVA OB.Cells(J, 13).Value = TVA(I, 7) 'retrieves in cell row J column 13 of sheet OB the value of data row I column 6 of TVA TEST = True 'redefines the variable TEST Exit For 'exits loop 2 End If 'end of the condition If TEST = False Then 'if TEST is [true] (the row does not exist in B) PLV = OB.Cells(Application.Rows.Count, "A").End(xlUp).Row + 1 'defines the first empty line PLV of column A of sheet OB OB.Cells(PLV, 1).Value = TVA(I, 4) 'retrieves in cell row PLV column 1 of sheet OB the value of data row I column 4 of TVA OB.Cells(PLV, 6).Value = Split(TVA(I, 2), "-")(1) 'retrieves in cell row PLV column 6 of sheet OB the value of data row I column 2 of TVA (after the dash) OB.Cells(PLV, 7).Value = TVA(I, 1) 'retrieves in cell row PLV column 7 of sheet OB the value of data row I column 1 of TVA OB.Cells(PLV, 8).Value = TVA(I, 3) 'retrieves in cell row PLV column 8 of sheet OB the value of data row I column 3 of TVA OB.Cells(PLV, 9).Value = TVA(I, 5) 'retrieves in cell row PLV column 9 of sheet OB the value of data row I column 5 of TVA OB.Cells(PLV, 11).Value = TVA(I, 6) 'retrieves in cell row PLV column 11 of sheet OB the value of data row I column 6 of TVA OB.Cells(PLV, 13).Value = TVA(I, 7) 'retrieves in cell row PLV column 13 of sheet OB the value of data row I column 7 of TVA Exit For 'exits loop 2 End If 'end of the condition Next J 'next line of loop 2 Next I 'next line of loop 1 MsgBox "Data processed!" 'message End Sub


See you,
ThauTheme
0