## 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:

- Type -1 into an unused cell on your worksheet
- Copy that cell
- Select the cells whose sign you want to change
- 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 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.

or ?

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

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

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

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

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

@Jeff

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

For Each ar In Columns(1).SpecialCells(-4123, 1)

ar.Formula = Replace(ar.Formula, "=", "=-(") & ")"

Next

End Sub

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

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

perhaps:

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

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.

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

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

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.

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

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.

That doesn’t happen for me. I’m using Excel 2013.

http://screencast.com/t/p22pHgWpV