Excel VB | positive to negative & vice versa

There are a few ways of doing this.

  1. The simplest way to do this without any VB is as follow (it also will do negative to positive).
    1. Add -1 to a cell on your sheet
    2. Copy that cell
    3. Select the range that you want to change the sign on
    4. Select “paste special” and from the operation section select multiple and click ok
  2. You could multiple the range of numbers by another cell reference. This can be done manually or via VB. Here is some code that will do this for you (in a workbook download at end of this post).
  3. Sub ApplyAFormulaToCell()
    On Error GoTo ENDER

    StringAction = Application.InputBox(“Type the formula to apply on the cells you have selected,” & vbNewLine & “i.e  ‘/1000′, ‘+4′ or ‘*2.5′” & vbNewLine & “use the point as decimal separator. You can also use cell references (select with a mouse).”, “Apply formula”, “/100″, , , , , 2)

    If StringAction = “” Then Exit Sub
    If StringAction = False Then Exit Sub

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    CellLength = Selection.Cells.Count
    i = 1

    For Each rngCel In Selection

    If rngCel.Formula <> “” Then
    Application.StatusBar = “Processing: ” & StringAction & ” ” & Int(i * 100 / CellLength) & “%”
    StringFormula = rngCel.FormulaR1C1

    If Left(StringFormula, 1) = “=” Then StringFormula = Right(StringFormula, Len(StringFormula) – 1)
    If rngCel.Value <> “” And IsNumeric(rngCel.Value) Then
    rngCel.Formula = “=(” & StringFormula & “)” & StringAction
    End If
    Else
    End If

    i = i + 1

    Next rngCel

    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    Exit Sub

    ENDER:
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    MsgBox “An error occured.” & vbNewLine & “Please check your formula: ‘ ” & StringAction & ” ‘”, vbCritical
    End Sub

  4. VBA addin – this is for me not the simplest way to achieve this but it certainly the most convenient. The VBA is included in the example workbook at the bottom of this post. The added value you achieve with this is that you can add it to an addin and have it on a shortcut key.Plus I have three options with it 1 & 2 are convert all to positive or negative. This is something that you can’t achieve with the above 2 solutions. 3rd option is invert positive to negative or negative to positive (as with the above selections). Here is the code for the form:
  5. Option Explicit
    Dim RngCell               As Range
    Dim RngSelection         As Range
    Dim CellLen               As Long
    Dim i                    As Long

    Private Sub cmdCancel_Click()
    Unload Me
    End Sub

    Private Sub cmdOK_Click()
    Application.ScreenUpdating = False
    Set RngSelection = selection
    selection.SpecialCells(xlCellTypeConstants, 1).Select
    CellLen = selection.Cells.Count
    i = 1
    If OptPositive = True Then Call ChangetoPositive
    If OptNegative = True Then Call ChangetoNegative
    If OptFlip = True Then Call Flip
    Application.StatusBar = False
    Application.ScreenUpdating = True
    RngSelection.Select
    MsgBox “Numbers converted”, vbInformation, “SP” & ” – Convert numbers”
    End Sub

    Sub ChangetoPositive()
    For Each RngCell In selection
    Application.StatusBar = “Converting all numbers to positive :   ” & Int(i * 100 / CellLen) & “%”
    If IsNumeric(RngCell.Value) Then
    If RngCell.Value < 0 Then RngCell.Value = RngCell.Value * -1
    End If
    i = i + 1
    Next
    End Sub

    Sub ChangetoNegative()
    For Each RngCell In selection
    Application.StatusBar = “Converting all numbers to negative :   ” & Int(i * 100 / CellLen) & “%”
    If IsNumeric(RngCell.Value) Then
    If RngCell.Value > 0 Then RngCell.Value = RngCell.Value * -1
    End If
    i = i + 1
    Next
    End Sub

    Sub Flip()
    For Each RngCell In selection
    Application.StatusBar = “Converting all numbers to negative :   ” & Int(i * 100 / CellLen) & “%”
    If IsNumeric(RngCell.Value) Then
    RngCell.Value = RngCell.Value * -1
    End If
    i = i + 1
    Next
    End Sub

Here is the workbook with the above code in. Feel free to change it but if you use it please add my credit. Also if you have any other cool ways of doing this let me know. Thanks

excel

converting-positive-negative.zip