Convert negative values treated as text
1999-12-20 Other 0 156
When you import numbers from data sources outside Excel it sometimes happens that the negative values is treated as text if they have the minus sign after the value. With the macro below you can convert these negative numbers to a valid negative value Excel can perform calculations with:
Sub ConvertNegativeNumbers() Dim cl As Range If TypeName(ActiveSheet) <> "Worksheet"Then Exit Sub Application.StatusBar = "Converting negative values..." For Each cl In Cells.SpecialCells(xlCellTypeConstants, xlTextValues) If Right(cl.Formula, 1) = "-" Then On Error Resume Next cl.Formula = CDbl(cl.Value) On Error GoTo 0 End If Next cl Set cl = Nothing Application.StatusBar = False End Sub