What my code do is that it will automatically get all the Excel files list in the folder and compare the Serial number on master list column then if the value is same it will copy the serial number and paste it on the C column. If both of the column formats are the same then it will work perfectly. But if the column is formatted in text, the comparison seems to fail. I've been searching quite some time to convert these cells to number format or general format so that the comparison can be successfully done without the concern on the format.
Sub Compare()
Dim Dic As Object
Dim fso As Object
Dim fldStart As Object
Dim fl As Object
Dim Mask As String, i As Long
Dim Wbk As Worksheet, w1 As Worksheet
Set fso = CreateObject("scripting.FileSystemObject")
Set fld = fso.GetFolder("C:\Users\kelvinwong\Desktop\Survey Testing")
Set w1 = Workbooks("Book1.xlsm").Sheets("Sheet1")
Set Dic = CreateObject("Scripting.Dictionary")
Mask = "*.xlsx"
For Each fl In fld.Files
If fl.Name Like Mask Then
Set Wbk = Workbooks.Open(fld & "\" & fl.Name).Sheets("Sheet1")
i = Wbk.Cells.SpecialCells(xlCellTypeLastCell).Row
For Each oCell In Wbk.Range("A2:A" & i)
If Not Dic.exists(oCell.Value) Then
Dic.Add oCell.Value, oCell.Offset(, 0).Value
End If
Next oCell
End If
Next fl
i = w1.Cells.SpecialCells(xlCellTypeLastCell).Row
For Each oCell In w1.Range("A2:A" & i)
For Each key In Dic
If oCell.Value = key Then
oCell.Offset(, 2).Value = Dic(key)
End If
Next
Next
End Sub
