» Convert negative values treated as text using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions 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 ConvertNegNumbers()
Dim cl As Range, a As Integer
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    Application.ScreenUpdating = False
    Application.StatusBar = "Converting negative values..."
    a = Selection.Areas.Count
    If a = 1 And Selection.Cells.Count = 1 Then ActiveSheet.UsedRange.Select
    For a = 1 To Selection.Areas.Count
        For Each cl In Selection.Areas(a).Cells
            If Right(cl.Formula, 1) = "-" Then
                cl.Formula = "-" & Left(cl.Formula, Len(cl.Formula) - 1)
            End If
        Next cl
    Next a
    Application.StatusBar = False
    Application.ScreenUpdating = True
End Sub