Convert negative values treated as text

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

Document last updated 1999-12-20 12:51:27 Printerfriendly version