CODEWITHSUNDEEP
excelvba
Kelvin Wong
Kelvin Wong

Reputation: 45

Excel VBA How to convert cell format text to number

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.

enter image description here

enter image description here

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

Upvotes: 3

Views: 2784

Answers (2)

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3378

Try to add the following line to your code: Columns("A").TextToColumns. It works.

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

Upvotes: 3

Siddharth Rout
Siddharth Rout

Reputation: 149335

Another way

Let's say your range is from A1:A15 then you can do this as well

[A1:A15] = [INDEX(INT(A1:A15),)]

To understand what this does, you can see This post

Upvotes: 0

Related Questions