Convert negative values treated as text using VBA in Microsoft Excel

Follow by Email

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
Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *

To avoid automated spam,Please enter the value *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>