Changing the Sign of a Cell

You probably already know the trick for changing the signs on a bunch of cells. But if not, here it is:

  1. Type -1 into an unused cell on your worksheet
  2. Copy that cell
  3. Select the cells whose sign you want to change
  4. Paste Special – Multiply

I do this quite a bit. Recently when I had to enter a pretty long list of numbers, most of which were negative, I decided to enter them with their sign reversed (for ease of entry) and employ this technique. Then I thought that it was just too much work. So I wrote a macro. It doesn’t do exactly what the Copy – Paste Special – Multiply trick does, but arguably does it better.

Private Const msFORMADD As String = ")*-1"
Private Const msFORMST As String = "=("

Sub ChangeSign()
   
    Dim rCell As Range

    gclsAppEvents.AddLog "^+n", "ChangeSign"
   
    If TypeName(Selection) = "Range" Then
        For Each rCell In Selection.Cells
            If CellCanChangeSign(rCell) Then
                If rCell.HasFormula Then
                    If CellFormulaHasSignChange(rCell) Then
                        rCell.Formula = RemoveFormulaSignChange(rCell.Formula)
                    Else
                        rCell.Formula = Replace(rCell.Formula, "=", msFORMST, 1, 1) & msFORMADD
                    End If
                ElseIf IsNumeric(rCell.Value) Then
                    rCell.Value = -rCell.Value
                End If
            End If
        Next rCell
    End If
   
End Sub

Function CellCanChangeSign(rCell As Range) As Boolean
   
    CellCanChangeSign = rCell.Address = rCell.MergeArea.Cells(1).Address And Not IsEmpty(rCell.Value)
   
End Function

Function CellFormulaHasSignChange(rCell As Range) As Boolean
   
    CellFormulaHasSignChange = Left$(rCell.Formula, Len(msFORMST)) = msFORMST _
        And _
                                Right$(rCell.Formula, Len(msFORMADD)) = msFORMADD
       
End Function

Function RemoveFormulaSignChange(ByVal sFormula As String) As String
   
    Dim sReturn As String
   
    sReturn = Left$(sFormula, Len(sFormula) - Len(msFORMADD)) 'remove last characters
    sReturn = Replace$(sReturn, msFORMST, "=", 1, 1) 'remove first paren
   
    RemoveFormulaSignChange = sReturn
   
End Function

ChangeSign is the entry point procedure and is called with Ctrl+Shift+n (more on what AddLog is in a later post). After it determines that there is a range selected (and not a shape, for example), it calls CellCanChangeSign to make sure it’s OK to move forward. In CellCanChangeSign I try to deal with merged cells. I don’t use merged cells a ton, so I can never remember how to avoid problems in VBA when dealing with them. I decided that if the cell was the first cell in the merge area, that was good enough. If the cell isn’t merged, its MergeArea is equal to itself, so this would still return True. Also, I don’t want to do anything to empty cells so I make sure that’s not the case.

Once I verify that the cell is good to go, I treat cells with formulas different than cells with values. When you use Paste Special – Multiply, Excel takes whatever formula you have and changes the formula to multiply by -1. For example, =SUM(F6:F14) becomes =(SUM(F6:F14))*-1. I’m not sure why it doesn’t negate the formula with -(formula), but it doesn’t. I made mine match what Excel does.

One problem with changing the signs of formulas is that if you do it twice, then =SUM(F6:F14) becomes =((SUM(F6:F14))*-1)*-1. Of course what else could it do. It’s a multiplying operation, not a toggle. I tried to make mine a toggle at least where I could. If you monkey with the formula between operations, you may get multiple multiplications, but if you just run the code twice it will remove what it did. I’ve tested this nearly a half dozen times, so I’m sure it’s rock solid.

CellFormulaHasSignChange checks that "=(" is at the start of the formula and ")*-1" is at the end. If they are then [cci]RemoveFormulaSignChange[cci] removes them and sets the formula back to its original.

For cells with numbers and not formula, the Value is simply negated.

13 Comments

  1. snb says:

    or ?

    Sub M_snb()
        For Each ar In Columns(1).SpecialCells(2, 1)
           ar.Value = -1 * ar
        Next
       
        For Each ar In Columns(1).SpecialCells(-4123, 1)
           ar.Replace "=", "=-"
        Next
    End Sub
  2. Jeff Weir says:

    snb: your approach doesn’t work on formulas like this:
    = 1 + 1

    Also, for anyone wondering what those mysterious numbers in SpecialCells are:
    1 = xlNumbers
    2 = xlCellTypeConstants
    -4123 = xlCellTypeFormulas

  3. Jeff Weir says:

    Dick: There’s a great procedure to employ at a bank as part of an April Fools trick. Get it to fire whenever the user does a cut and paste, along with the following custom number format:
    “-”##0;##0;0

  4. If you are willing to give up your “toggle” and just have the code do exactly what Excel does…

    Sub ChangeSign()
      With Cells(Rows.Count, "A").End(xlUp).Offset(1)
        .Value = -1
        .Copy
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
                               SkipBlanks:=False, Transpose:=False
        .Clear
      End With
    End Sub
  5. snb says:

    @Jeff

    I always assumed everybody has the object browser (F2) in the VBEditor available.

    Sub M_snb()
        For Each ar In Columns(1).SpecialCells(-4123, 1)
            ar.Formula = Replace(ar.Formula, "=", "=-(") & ")"
        Next
    End Sub
  6. Jeff Weir says:

    Everybody has access to an English dictionary, but unless they consult it this sentence might well be Glossolalia to them.

  7. Ioannis Varlamis says:

    The Sub M_snb does not work for functions that return an error. Also, Ctrl+Shift+Enter functions are converted to Enter functions
    perhaps:

    Sub ChangeSign()
    For Each ar In Columns(1).Cells
    If ar.HasArray Then
    ar.FormulaArray = Replace(ar.FormulaArray, "=", "=-(") & ")"
    ElseIf ar.HasFormula Then
    ar.Formula = Replace(ar.Formula, "=", "=-(") & ")"
    End If
    Next
    End Sub
  8. Ryan says:

    I’ve used something like this for a few years. There is an evil edge case where a number has a trailing minus sign. Isnumeric will return true which may or may not be the behavior expected depending on the source data.

  9. Dick Kusleika says:

    Beware of ListColumns with formulas. I just applied this to a column in a ListObject (with 20k rows) that had a formula. See if you can guess what happened. :)

    If you can’t get guess, type this into Excel

    Name Number
    Tom =ROW()
    Dick =ROW()
    Harry =ROW()
    Joe =ROW()

    Ctrl+T to make it a table. Select the Number column and run this procedure.

  10. Dick Kusleika says:
    Function CellCanChangeSign(rCell As Range, rSel As Range) As Boolean
       
        Dim bReturn As Boolean
       
        bReturn = rCell.Address = rCell.MergeArea.Cells(1).Address
        bReturn = bReturn And Not IsEmpty(rCell.Value)
        bReturn = bReturn And Not (Not rCell.ListObject Is Nothing And rCell.Row > rSel.Cells(1).Row And rCell.HasFormula)
       
        CellCanChangeSign = bReturn
       
    End Function

    That seems to fix the ListObject problem. How about that last row for true code readability. It says if the cell is part of a table, and it’s in the first row of the selection and it has a formula, then change it. That should ensure that only one cell per formula-containing-ListColumn gets changed.

  11. Jeff Weir says:

    What’s supposed to happen? I ran your sub on the suggested table, and it worked just fine.

  12. Dick Kusleika says:

    When I ran it, it changed the first cell, which changed all the other cells in the column as Tables do. Then it changed the second cell (which was already changed as a result of the first) and that in turn changed all the other cells. With four cells, it changes and changes back twice leaving them at their original formulas.

  13. Jeff Weir says:

    That doesn’t happen for me. I’m using Excel 2013.
    http://screencast.com/t/p22pHgWpV

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: