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.