Negating a Cell Using a Macro

Written by Allen Wyatt (last updated November 6, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


21

Sam would like to create a macro to negate whatever value is in a cell. In other words, if the cell contains 10, after running the macro the cell will contain -10. He tried using the macro recorder to record his actions—pressing F2, the Home key, entering a minus, and then pressing Enter. However, the recorded macro places the exact same amount (-10) into the cell, regardless of what was there before. So, Sam is wondering how he can create the negation macro he wants.

This occurs because the macro recorder records what was entered into the cell, not individual keystrokes. This means that the only way to accomplish the task is to create the macro from scratch instead of using the recorder.

The solution to Sam's issue is to figure out exactly what is meant by "negating" a cell. If the task is to simply change the sign of a numeric value in a cell (such that 5 becomes -5 or -23 becomes 23), then the following macro will work just fine:

Sub NegateSelection1()
    Dim rCell As Range

    For Each rCell In Selection
        If IsNumeric(rCell) And rCell <> 0 Then
            rCell = -rCell
        End If
    Next rCell
End Sub

To use the macro, select a cell or range of cells in your worksheet, and then run it. You could, of course, assign it to a shortcut key or add it to the ribbon or Quick Access Toolbar.

Note that the macro does two tests. It first checks to see if the cell is "numeric," but that is not enough. Why? Because if the cell is empty, it is still considered "numeric" by Excel, so you end up with a 0 value in the cell. The solution is the second test, to make sure that Excel doesn't consider the cell containing a 0 value. (Yes, Excel considers the cell to contain a 0 value if it is empty.) You could also use the IsEmpty function for your test, as well:

Sub NegateSelection1()
    Dim rCell As Range

    For Each rCell In Selection
        If IsNumeric(rCell) And Not IsEmpty(rCell) Then
            rCell = -rCell
        End If
    Next rCell
End Sub

If the task is to make sure that all the values in a selection are negative (such that 5 becomes -5 and -23 doesn't change), then this variation of the macro will work:

Sub NegateSelection2()
    Dim rCell As Range

    For Each rCell In Selection
        If IsNumeric(rCell) And Not IsEmpty(rCell) Then
            If rCell > 0 Then rCell = -rCell
        End If
    Next rCell
End Sub

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10127) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Hiding Columns Not within a Date Range

Want to automatically hide some columns that don't meet a date criterion that you set? You can't do it automatically, but ...

Discover More

Getting Rid of Background Color in All Tables

When working with tables (particularly those created by others), you can spend a large amount of time getting the ...

Discover More

Shortening Worksheet Tabs

When worksheet names are quite long, it can present problems in displaying those names in the tabs at the bottom of the ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Macro Runs Slowly, but Steps Quickly

When you have a macro that processes a huge amount of data, it can seem like it takes forever to finish up. These ...

Discover More

Disabled Macros

Do your macros seem to be disabled on your new machine? It could be because of the security settings in Excel. Here's ...

Discover More

Saving an Unsavable Workbook

Macros can allow you to do some fancy data validation in your workbooks, such as checking to see if the user entered ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 3?

2021-12-14 09:10:25

Philip

Could this not be resolved without macro's (albeit by using a helper column) ? Something along the lines of the following formula in helper B1

=IFERROR(-1*(A1),A1)

Where A1 contains the value you want to negate, and pull this down as far as you need ?

Or am I missing the point here ?


2021-11-13 16:03:15

J. Woolley

Will these comments never end? Here is an improved version of NegateSelection5, with thanks to Tomek (see Figure 1 below) .
Note: SpecialCells(xlCellTypeConstants, xlNumbers) and UsedRange include hidden cells,
but SpecialCells(xlCellTypeLastCell) ignores hidden cells.

Figure 1. 


2021-11-13 12:49:32

J. Woolley

Oops. In NegateSelection5 below, add the following line after "Next Cell"
    If Targ Is Nothing Then Exit Sub


2021-11-13 12:08:46

J. Woolley

The following macro has the same characteristics as NegateSelection3 in my previous comment, but this macro is almost 8-times faster when Selection contains many cells. And it has the useful result that only negated cells remain selected, so they are easily identified. For some reason, I can't post it in the usual way, so I am posting a screenshot instead (see Figure 1 below) .

Figure 1. 


2021-11-12 13:12:17

J. Woolley

If there are many cells in Selection, the following macro is almost 90 percent faster then NegateSelection3 (below) and has the useful result that only negated cells remain selected.Sub NegateSelection5()    Dim Cell As Range, Targ As Range, Temp As Range    For Each Cell In Selection        If IsNumeric(Cell.Formula) Then            If Not IsDate(Cell) Then                If Targ Is Nothing Then                    Set Targ = Cell                Else                    Set Targ = Union(Targ, Cell)                End If            End If        End If    Next Cell    With Selection.Parent.UsedRange        Set Temp = Cells((.Row + .Rows.Count), 1)    End With    Temp.Value = -1    Temp.Copy    Targ.PasteSpecial Paste:=xlPasteValues, _        Operation:=xlPasteSpecialOperationMultiply    Application.CutCopyMode = False    Temp.ClearEnd Sub


2021-11-12 13:07:08

J. Woolley

At the risk of flogging a dead horse, here is a revision of my NegateSelection3 macro. It negates all cells in Selection that contain a numeric constant. It ignores cells with text, date, or logical (TRUE/FALSE) constants, empty cells, and all cell formulas including array formulas (CSE or dynamic). A date with or without time is ignored, but a time without date is negated (unfortunately).

Sub NegateSelection3()
    Dim Cell As Range
    For Each Cell In Selection
        If IsNumeric(Cell.Formula) Then
            If Not IsDate(Cell) Then
                Cell = -Cell
            End If
        End If
    Next Cell
End Sub


2021-11-09 20:22:15

Tomek

@J. Woolley:
I agree that it is simplest and actually close to what Sam asked for.

I have a tendency to read into the questions that Allen posts in his newsletter much more than was asked, then answer the expanded questions. That's where the macro to keep the formula came from.

The simplest solutions are usually best, that should be my motto, but it's hard to keep your own advice.


2021-11-09 17:49:38

J. Woolley

@Tomke
All things considered, I think my NegateSelection3 macro is best. See 2021-11-06 11:10:54 below.


2021-11-07 09:30:14

Tomek

Here is a macro that combines J. Wooley's ideas and mine.
Please read our previous comments to understand what it does.
-----------------------------------------------------------
Public Sub PasteNegate2()
Dim rTemp As Range
On Error GoTo errHandler

ActiveSheet.UsedRange 'resets used range
Set rTemp = Cells.SpecialCells(xlLastCell).Offset(1, 1)
'cell in next row and column after all data.
rTemp = -1
rTemp.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationMultiply
Application.CutCopyMode = False

rTemp.Delete Shift:=xlToLeft
'rTemp.Clear 'is not good enough to reset used range!
Set rTemp = Nothing
ActiveSheet.UsedRange

'optional cleanup
For Each Cel In Selection
If Left(Cel.Formula, 3) = "=((" And Right(Cel.Formula, 8) = ")*-1)*-1" Then
Cel.Formula = "=" & Mid(Cel.Formula, 4, Len(Cel.Formula) - 11)
End If
Next Cel
Exit Sub

errHandler:
If Err.Number = 1004 Then
MsgBox "One of celected cells contains array formula. " & _
"Please deselect it."
Else
MsgBox "One of the selected cells cannot be processed. " & _
"Try selecting cells one by one to identify which cannot be processed."
End If
Resume Next

End Sub


2021-11-06 13:37:27

Tomek

@J. Wooley
Re: indenting, I also figured out to use non-breaking spaces and once the code was ready, used Ctrl+H to replace four regular spaced with for with 4 non-breaking spaces in word, then copy paste.
Another option I used is to replace four regular spaces with four (or more) html codes for nonbreaking space (& nbsp;).
isn't there an easier way?
&  Tomek


2021-11-06 13:14:13

J. Woolley

@Tomek
Re. indenting, see David Bonin's 2021-01-18 comment here:
https://excelribbon.tips.net/T011598_Limiting_Who_Can_Delete_Data.html
I use Ctrl+H in Notepad to replace 4 leading space chars with 4 Alt+0160 non-breaking space chars or with 2-pair of 1 non-breaking space plus 1 standard space. You can also use Ctrl+Shift+Space in Word for a non-breaking space char, which is visible when toggled with Ctrl+Shift+*.


2021-11-06 13:07:09

Tomek

@J. Wooley
I tried creating a range named Negative1 but instead of referring it to a cell, I tried referring it to =-1. The range was created, but it couldn't be used in the set statement nor in the copy statement.

Any thoughts on this?


2021-11-06 13:03:31

Tomek

@J. Wooley
I just noticed that you too created programmatically a range before copying it to the clipboard. ;-)


2021-11-06 12:36:58

Tomek

@J. Wooley
I did not see your comment while composing mine. My approach is very similar, only I couldn't figure out how to place -1 in the clipboard without copying an actual cell.
I added some extra code to cleanup expanding formula and also to prevent run-time error when any of the cells contain array formula. One such formula is =CELL(info, ref). It does not look like an array but Excel considers it to be. Your code suffers from this too. I suggest we try to combine our codes in one.

I have another question for you: How did you achieve indented code in the comment. I managed to get it too, but had to jump through some hoops. To take it off line, please respond by e-mail to me, my address is unhidden.


2021-11-06 12:25:00

Tomek

All of the macros give by Allen will work fine with just numbers and with formulas resulting in the number. However the result will be negated numbers in the selected cells and any formulas in those cells will be gone.
To keep the formulas and just change the sign of the results there is a very easy way: copy a cell containing -1 into clipboard and use Paste Special Values with Operation Multiply into the target cells. The macro that does this is shown below.
To use the macro, you need to place -1 in a cell that will not interfere with your data and create a named range "Negative1" referring to this cell. My suggestion is to select a cell in the top row of the spreadsheet, name it, then hide Row 1 to minimize a chance of inadvertent changes to this cell. The macro will target all cells in current selection, so you may change signs of several cells at once, similarly to the macros given in the tip.
The macro will negate the explicit numbers, multiply any formulas that return a number by -1, and will ignore any cells that contain plain text. If the formula returned text or empty string the cell will display #VALUE!. There is no such thing as -text. This applies to simple formulas like =22 or =A1, as well as to formulas containing nested functions and even logical tests (IF formulas).
The purpose of the second part of the macro is to do some cleanup of what otherwise would clutter the formula cell(s), which the macro modified. Each paste special operation adds a bracket at the beginning of the formula, another one at the end and follows it with "*-1". After several Paste Special operations the formula might look like this: =((((((((((((B6)*-1)*-1)*-1)*-1)*-1)*-1)*-1)*-1)*-1)*-1)*-1)*-1. Once the operation was <b>carried out twice</b>, the for-next loop will remove two starting brackets and ")*-1)*-1" from the end, returning the formulas in all cells in the selection to the original ones. After all, multiplying by -1 twice is kind of nul operation. As an added benefit, the formulas that did not return a number will also be reverted to originals, avoiding the #VALUE! errors.
The Paste Special operation will generate an error if any cell in the selection contains an array formula. This error is trapped in the macro; a message will be displayed and the contents of the cells will not be changed.
Blank cells are treated as zeros and changed to 0 (0*-1 = 0). To avoid this, just do not select blank cells before running the macro. It is not worth adding code to the macro to exclude blank cells, but it could be done.
Here is the macro:
Public Sub PasteNegate()
'multiplies formula in each cell of the selection by the formula in range "Negative1"
'Negative1 is intended to have -1 value in it
On Error GoTo errHandler

        Range("Negative1").Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
        Application.CutCopyMode = False
        
        'optional cleanup
        For Each Cel In Selection
        If Left(Cel.Formula, 3) = "=((" And Right(Cel.Formula, 8) = ")*-1)*-1" Then
                        Cel.Formula = "=" & Mid(Cel.Formula, 4, Len(Cel.Formula) - 11)
                End If
        Next Cel
Exit Sub

errHandler:
If Err.Number = 1004 Then
        MsgBox "One of celected cells contains array formula. Please deselect it."
Else
        MsgBox "One of the selected cells cannot be processed. Try selecting cells one by one to identify which cannot be processed."
End If
Application.CutCopyMode = False

End Sub


2021-11-06 11:32:41

J. Woolley

If there are many cells in Selection, this macro is fast. It will negate formulas as well as constants. It will not disturb text constants, but empty cells will become zero and formulas that returned text will return #VALUE! instead:

Sub NegateSelection4()
    Dim Temp As Range
    With ActiveSheet.UsedRange
        Set Temp = Cells((.Row + .Rows.Count), 1)
    End With
    Temp.Value = -1
    Temp.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlPasteSpecialOperationMultiply
    Application.CutCopyMode = False
    Temp.Clear
End Sub

See https://sites.google.com/view/MyExcelToolbox/


2021-11-06 11:15:23

J. Woolley

@Tomek
Willy's macro works fine if Selection contains only numeric values.


2021-11-06 11:10:54

J. Woolley

Willy's macro is very clever, but not if Selection contains an empty or non-numeric cell.
Willy's macro and the Tip's macros convert numeric formulas into negated constants, which might not be intended.
This macro avoids those problems:

Sub NegateSelection3()
    Dim rCell As Range
    For Each rCell In Selection
        If IsNumeric(rCell.Formula) Then
            rCell = -rCell
        End If
    Next rCell
End Sub

See https://sites.google.com/view/MyExcelToolbox/


2021-11-06 10:51:13

Tomek

Just a note that IsNumeric VBA function is slightly different from the worksheet function ISNUMBER.
    ·IsNumeric returns TRUE for Boolean TRUE and FALSE while ISNUMBER returns false.
    ·IsNumeric returns False for numbers formatted as a date but not as just time! ISNUMBER returns TRUE for both.
    ·For blank cells IsNumeric returns TRUE, but ISNUMBER returns FALSE.


2021-11-06 10:49:39

Tomek

@willy Vanhaelen:
Your macro does not work. It results in value of FALSE being put in every selected cell!


2021-11-06 07:14:18

Willy Vanhaelen

Here is a one-liner for the first macro avoiding a loop:

Sub NegateSelection1()
Selection = Evaluate(Replace("IF(AND(ISNUMBER(#),#<>0),-#)", "#", Selection.Address))
End Sub


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.