Change negative numbers to positive numbers and positive numbers to negative numbers in Excel by using VBA. These macros reverse the sign of cells in Excel.
The first macro was submitted by wellsrPRO community member, Jomili, and the second macro is a slight modification I made to handle non-numeric. All you have to do is select the cells you want to negate, and run the macro of your choice.
Reader’s Note: This article is part of a series featuring macros submitted by my incredible wellsrPRO community members. These articles are similar to my Code Library articles in that they usually won’t contain an accompanying detailed tutorial.
If you’re an existing wellsrPRO member, don’t forget to submit your own macro to the wellsrPRO community using the Share My Macros button.
wellsrPRO users can automatically import this community submission directly into their spreadsheet. Just look for “Community Submissions” in the Auto-Import dropdown menu.
Reverse Sign of Cells in Excel
Created by Jomili
Sub PlusMinus() 'DEVELOPER: Submitted by wellsrPRO community member "Jomili" 'Changes the sign on a selected range of numbers Dim cell As Range If Application.Count(Selection.Cells) = 1 Then MsgBox "You CAN'T be that Lazy! Select more cells!", vbOKOnly + vbExclamation, "Give Me a Break" Exit Sub End If On Error Resume Next 'copes with cells that are not numeric For Each cell In Selection.SpecialCells(xlCellTypeConstants, 23) cell.Value = -cell.Value Next cell End Sub
If you have a lot of cells selected, you’ll want to pair this macro with SpeedOn and SpeedOff subroutines previously submitted by Jomili to speed up your macro.
There’s a lot to unpack here. To save time and become really good at VBA, take a look at our VBA Reference Guides. They have over 180 tips and 135 pre-built macros covering the 100 most important topics in VBA.
Enhanced way to change signs in Excel
Sub PlusMinus_modified() 'DEVELOPER: Ryan Wells (wellsr.com) 'Changes the sign on a selected range of numbers Dim cell As Range Dim v As Variant If Application.Selection.Cells.Count = 1 Then v = MsgBox("Only one cell is selected. Are you sure you want to change the sign of only one cell?", vbYesNo, "Change 1 cell?") If v = vbNo Then Exit Sub End If For Each cell In Intersect(Selection, Selection.SpecialCells(xlCellTypeConstants, 23)) 'negate values in selection, excluding formulas If IsNumeric(cell.Value) Then cell.Value = -cell.Value 'change negative to positive and positive to negative Next cell End Sub
These macros will not reverse the signs of cells containing formulas and they won’t add a negative sign to text or dates. The VBA macros are smart enough to see if the cell is numeric before trying to negate them!
Instead of displaying a funny message, like Jomili did in the first macro, the second macro reminds the user that they only have one cell selected and it asks the user if they really want to change the sign of just that one cell. The
SpecialCells portion of the
For Each loop makes sure that formulas are ignored.
Intersect function is necessary because the SpecialCells method will expand beyond your original selection if you only have one cell selected. In other words, even if you had only one cell selected, it would try to negate all the cells in your spreadsheet! We wouldn’t want that, would we?
Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I developed this unique 3-part free Excel training program to help you quickly learn VBA in a natural setting: right inside Excel. I'm also going to give you my entire personal macro library for free.
If you’re ready to free up your time in the office, visit my Excel Add-ins page for some great automation products.