Excel VBA 如何将单元格格式文本转换为数字

Question

我的代码所做的是，它会自动获取文件夹中的所有 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

Answer 1

尝试将以下行添加到您的代码中:Columns("A").TextToColumns。它有效。

Sorry for the minor edit. Someone downvoted my answer that turned out to be correct. T_T