VBA Code to Change Text Formatted Cells to Numbers

VBA Code to Change Text Formatted Cells to Numbers

December 14, 2016

I tested out the below vba code today and it worked perfectly converting all text formatted cells on all worksheets in my spreadsheet to number formatted cells.    Formulas will not always search text formatted cells correctly.   As you can see this example, the VLOOKUP in column C does not successfully run through the text formatted numbers in column E. 

 

 

 

After we insert the VBA code and run it, the cells are converted to numbers, and the formula works correctly.

 

 

 

 

 

 

Sub ConvertTextNumberToNumber()

For Each WS In Sheets

On Error Resume Next

For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)

If IsNumeric(r) Then r.Value = Val(r.Value)

Next

Next

End Sub

 

 

This is available at: 

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/vba-code-to-convert-text-to-number/e2f5c527-4326-4448-a029-71298d1617c8

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com