VBA Code to Change Text Formatted Cells to Numbers

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