我的代码所做的是它将自动获取文件夹中的所有Excel文件列表并比较主列表列上的序列号,然后如果值相同,它将复制序列号并将其粘贴到C列上 . 如果两种列格式相同,那么它将完美地工作 . 但是如果列格式化为文本,则比较似乎失败 . 我一直在寻找将这些单元转换为数字格式或一般格式的方法,以便在不考虑格式的情况下成功完成比较 .
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
2 回答
尝试将以下行添加到您的代码中:
Columns("A").TextToColumns
. 有用 .其他方式
假设您的范围来自
A1:A15
,那么您也可以这样做要了解这一点,你可以看到This帖子