'TODO LEFT:
'INPUT DATA NEEDED:
'1.NUMBER OF WORKBOOK
'2.WORKBOOKS'S DIRECTORY
'
'+

Sub ReadDataFromCloseFile()

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
    ' TODO: WE MANUALLY OPEN EACH FILE, NEED GET DIRECTORY AND NUMBER OF FILE
    Dim workbookArr(2)

    Set workbookArr(0) = Workbooks.Open("/Users/toannd/Documents/Transaction_New.xlsx", True, False)
    Set workbookArr(1) = Workbooks.Open("/Users/toannd/Documents/Transaction_Fragment_1.xlsx", True, False)
    Set workbookArr(2) = Workbooks.Open("/Users/toannd/Documents/Transaction_Fragment_2.xlsx", True, False)

    '==================================================================

    ' GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK.
    Dim totalRowsNew As Integer
    Dim totalRowsOld1 As Integer
    Dim totalRowsOld2 As Integer

    totalRowsNew = workbookArr(0).Worksheets("Sheet1").Range("A1048576").End(xlUp).Row
    totalRowsOld1 = workbookArr(1).Worksheets("Sheet1").Range("A1048576").End(xlUp).Row
    totalRowsOld2 = workbookArr(2).Worksheets("Sheet1").Range("A1048576").End(xlUp).Row

    'Range("A23").Value = srcNew.Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

    ' Concentrate product's code and customer's code

    Dim myConcatenateCell As String

    myConcatenateCell = "AA" & "2" 'TODO: VISUALLY WE CHOOSE THIS CELL TO APPLY, NEED TO AUTO FIND IT LATER

    'TODO: ITERATION
    workbookArr(0).Worksheets("Sheet1").Range(myConcatenateCell & ":AA" & CStr(totalRowsNew + 1)).FormulaR1C1 = "=CONCATENATE(RC[-21],RC[-18])"
    workbookArr(1).Worksheets("Sheet1").Range(myConcatenateCell & ":AA" & CStr(totalRowsOld1 + 1)).FormulaR1C1 = "=CONCATENATE(RC[-21],RC[-18])"
    workbookArr(2).Worksheets("Sheet1").Range(myConcatenateCell & ":AA" & CStr(totalRowsOld2 + 1)).FormulaR1C1 = "=CONCATENATE(RC[-21],RC[-18])"

    ' Look up value, return 1 if existed same value, otherwise 0

    Dim myFirstColumn As Long
    Dim myLastColumn As Long
    Dim myColumnIndex As Long
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myVLookupResult As Variant
    Dim myTableArray As Range
    Dim lookUpValue As String

    myFirstColumn = 27 'TODO: FIND SUITABLE COLUMN
    myLastColumn = 27
    myColumnIndex = 1
    myFirstRow = 2
    myLastRow = totalRowsOld1

    With workbookArr(1).Worksheets("Sheet1")
        Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn))
    End With

    ' Iterate to check every (just filled in) transaction
    'TODO: NEED ITERATION EVERY FILE

    For i = 1 To 2
        With workbookArr(i).Worksheets("Sheet1")
            Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn))
        End With

        For j = 2 To totalRowsNew
            lookUpValue = workbookArr(0).Worksheets("Sheet1").Range("AA" & CStr(j)).Value
            myVLookupResult = Application.VLookup(lookUpValue, myTableArray, myColumnIndex, False)
            If (IsError(myVLookupResult)) Then
                workbookArr(0).Worksheets("Sheet1").Range("AB" & CStr(j)).Value = 0
            Else: workbookArr(0).Worksheets("Sheet1").Range("AB" & CStr(j)).Value = 1
            End If
        Next j
    Next i

    '==================================================================

    ' CLOSE THE SOURCE FILE.
    workbookArr(0).Close True             ' TRUE - SAVE THE SOURCE FILE.
    Set srcNew = Nothing

    workbookArr(1).Close False             ' FALSE - DON'T SAVE THE SOURCE FILE.
    Set srcOld1 = Nothing

    srcOld2.Close False             ' FALSE - DON'T SAVE THE SOURCE FILE.
    Set srcOld2 = Nothing

ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub