# Excel VB | positive to negative & vice versa

There are a few ways of doing this.

- The simplest way to do this without any VB is as follow (it also will do negative to positive).
- Add -1 to a cell on your sheet
- Copy that cell
- Select the range that you want to change the sign on
- Select “paste special” and from the operation section select multiple and click ok

- 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).
- 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:

Sub ApplyAFormulaToCell()

On Error GoTo ENDERStringAction = 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 SubApplication.ScreenUpdating = False

Application.Calculation = xlCalculationManual

CellLength = Selection.Cells.Count

i = 1For Each rngCel In Selection

If rngCel.Formula <> “” Then

Application.StatusBar = “Processing: ” & StringAction & ” ” & Int(i * 100 / CellLength) & “%”

StringFormula = rngCel.FormulaR1C1If 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 Ifi = i + 1

Next rngCel

Application.StatusBar = False

Application.Calculation = xlCalculationAutomatic

Exit SubENDER:

Application.StatusBar = False

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

MsgBox “An error occured.” & vbNewLine & “Please check your formula: ‘ ” & StringAction & ” ‘”, vbCritical

End Sub

Option Explicit

Dim RngCell As Range

Dim RngSelection As Range

Dim CellLen As Long

Dim i As LongPrivate Sub cmdCancel_Click()

Unload Me

End SubPrivate 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 SubSub 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 SubSub 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 SubSub 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

## About

You’re currently reading “Excel VB | positive to negative & vice versa”.

- Published:
- Tuesday, August 19th, 2008

- Author:
- Simon Page

- Category:
- Excel

## Related

## Categories

- Artwork (75)
- Design Inspiration (33)
- Design Resources (10)
- Excel (12)
- Filemaker (5)
- Gadgets (26)
- Music (3)
- Photography (3)
- Photoshop (1)
- Press (7)
- Random (5)
- Software (4)
- Video Gaming (15)
- Wallpaper (6)

## Featured

- Album Cover Artwork Inspiration
- Creative Movie Poster Inspiration
- Criss Cross - iPad and iPhone Wallpaper
- Design Inspiration | typography posters
- Exclusive iPad Wallpaper
- Futurism - Limited Edition Prints
- Geometric Silk Scarves
- Geometric Silk Scarves #2
- Inspiring Gallery of Video Game Concept Art
- International Year of Astronomy 2009 Posters
- International Year of Chemistry 2011 Prints
- iPad HD Retina Wallpaper

## No comments

Jump to comment form | comments rss | trackback uri