# 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).
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()
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 converting-positive-negative.zip