ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

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

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address