Convert negative values treated as text

 1999-12-20    Other    0    52

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


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.