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

Excel Macro to List Ranges on All Worksheets

If you've got a spreadsheet with a large number of worksheets, and you want to run a formula from one an all of them, but aren't sure of the respective data ranges on each, try this macro posted to the Contextures Blog of Debra Dalgleish.

So as we can see in this example we have three worksheets which each contain different ranges of data. Some of the ranges have blank cells within them.

Entering Visual Basic (ALT + F11) and inserting the below code into a module with set up the macro. When you run it, a new worksheet will be created showing the full ranges on each worksheet.

Sub ListSheetsRangeInfo() Dim ws As Worksheet Dim lCount As Long Dim wsTemp As Worksheet Dim rngF As Range Dim lFields As Long Dim strLC As String Dim strSh As String Dim sh As Shape Application.EnableEvents = False Application.ScreenUpdating = False On Error Resume Next Set wsTemp = Worksheets _ .Add(Before:=Sheets(1)) lCount = 2 lFields = 5 With wsTemp .Range(.Cells(1, 1), _ .Cells(1, lFields)).Value _ = Array( _ "Sheet Name", _ "Used Range", _ "Range Cells", _ "Shapes", _ "Last Cell") End With For Each ws In ActiveWorkbook.Worksheets If ws.Name <> wsTemp.Name Then strSh = "" strLC = ws.Cells _ .SpecialCells(xlCellTypeLastCell) _ .Address If ws.Shapes.Count > 0 Then For Each sh In ws.Shapes strSh = strSh & sh.TopLeftCell _ .Address & ", " Next sh strSh = Left(strSh, Len(strSh) - 2) End If With wsTemp .Range(.Cells(lCount, 1), _ .Cells(lCount, lFields)).Value _ = Array( _ ws.Name, _ ws.UsedRange.Address, _ ws.UsedRange.Cells.Count, _ strSh, _ strLC) 'add hyperlink to sheet name .Hyperlinks.Add _ Anchor:=.Cells(lCount, 1), _ Address:="", _ SubAddress:="'" & ws.Name _ & "'!A1", _ ScreenTip:=ws.Name, _ TextToDisplay:=ws.Name 'add hyperlink to last cell .Hyperlinks.Add _ Anchor:=.Cells(lCount, lFields), _ Address:="", _ SubAddress:="'" & ws.Name _ & "'!" & strLC, _ ScreenTip:=strLC, _ TextToDisplay:=strLC lCount = lCount + 1 End With End If Next ws With wsTemp .Range(.Cells(1, 1), .Cells(1, lFields)) _ .EntireColumn.AutoFit .Rows(1).Font.Bold = True End With

Application.EnableEvents = True Application.ScreenUpdating = True

End Sub

Dynamic Data Exchange and Excel

If you're working in Windows, and you find that Excel files won't open from Windows Explorer when you click on them (but will open just fine if you do a local open from inside Excel and browse to them) but instead get this error message:

You probably have a problem with your Dynamic Data Exchange setting. In Excel 2016 go to File . . . Options . . . Advanced tab and scroll down to the General section. Uncheck the box for 'Ignore other applications that use Dynamic Data Exchange (DDE)'. Dynamic Data Exchange controls how Windows applications interact with one other.

Alternatively if you are working with a lot of data in an Excel worksheet, it may function better if you check this off.