VBA: Convert Text to Number Multiple Columns-CodePudding

I have created a VBA code and when I run it, it changes for several columns the values from number to text: Screenshot

Then I saw on this webpage some solutions like the following, but they are not working for my case:

Sub ConvertTextToNumber()

With Range("O:AD") 
.NumberFormat = "General"
.Value = .Value 
End With

End Sub

Can you help me with this issue? My range is from O:AD and I would need to run it for an indefinite number of values. Please bear in mind I'm new with this so, I would really appreciate if you could show me step by step.

CodePudding user response：

Manually I usually place a 1 in a blank cell, copy it and PasteSpecial - Operation Multiply.

This multiplies each value by 1 forcing it to be recognised as a number.

With VBA it would look something like:

Sub Test()

    'Add a temporary sheet.
    'Place value in cell A1 and copy it.
    'You could use a spare cell on the existing sheet if you want.
    Dim wrkSht As Worksheet
    Set wrkSht = ThisWorkbook.Worksheets.Add
    
    wrkSht.Cells(1, 1) = 1
    wrkSht.Cells(1, 1).Copy
    
    'Use the copied number to multiply the text numbers.  
    'Update the range to suite.
    ThisWorkbook.Worksheets("Sheet1").Range("D2:D4").PasteSpecial Operation:=xlMultiply
    
    'Delete the temporary sheet.
    Application.DisplayAlerts = False
    wrkSht.Delete
    Application.DisplayAlerts = True
    
End Sub

Change Sheet1 to whatever the name of your sheet is.

CodePudding user response：

From the front end you can just do Data | Text to Columns.
Only one column at a time (front end or in macro).
You can record this as a macro and then add the looping through the multiple columns in the VBA editor.