by Allen Wyatt
(last updated November 6, 2021)
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
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 Office 365.
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!
Need to select a cell using a macro? Need that selection to be relative to the cell you currently have selected? Here are ...Discover More
When you use a macro to process data you always run the risk of making that data unusable by Excel. This is especially ...Discover More
Macros are very powerful, but you may not want them to always be available to a user. Here are some ways you can limit ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.