- \n
- Add -1 to a cell on your sheet<\/li>\n
- Copy that cell<\/li>\n
- Select the range that you want to change the sign on<\/li>\n
- Select “paste special” and from the operation section select multiple and click ok<\/li>\n<\/ol>\n<\/li>\n
- You could multiple the range of numbers by another cell reference. This can be done manually or via VB. Here is some code that will do this for you (in a workbook download at end of this post).<\/li>\n
Sub ApplyAFormulaToCell()
\nOn Error GoTo ENDER<\/p>\nStringAction = Application.InputBox(“Type the formula to apply on the cells you have selected,” & vbNewLine & “i.e\u00a0 ‘\/1000’, ‘+4’ or ‘*2.5′” & vbNewLine & “use the point as decimal separator. You can also use cell references (select with a mouse).”, “Apply formula”, “\/100”, , , , , 2)<\/p>\n
If StringAction = “” Then Exit Sub
\nIf StringAction = False Then Exit Sub<\/p>\nApplication.ScreenUpdating = False
\nApplication.Calculation = xlCalculationManual
\nCellLength = Selection.Cells.Count
\ni = 1<\/p>\nFor Each rngCel In Selection<\/p>\n
If rngCel.Formula <> “” Then
\nApplication.StatusBar = “Processing: ” & StringAction & ” ” & Int(i * 100 \/ CellLength) & “%”
\nStringFormula = rngCel.FormulaR1C1<\/p>\nIf Left(StringFormula, 1) = “=” Then StringFormula = Right(StringFormula, Len(StringFormula) – 1)
\nIf rngCel.Value <> “” And IsNumeric(rngCel.Value) Then
\nrngCel.Formula = “=(” & StringFormula & “)” & StringAction
\nEnd If
\nElse
\nEnd If<\/p>\ni = i + 1<\/p>\n
Next rngCel<\/p>\n
Application.StatusBar = False
\nApplication.Calculation = xlCalculationAutomatic
\nExit Sub<\/p>\nENDER:
\nApplication.StatusBar = False
\nApplication.Calculation = xlCalculationAutomatic
\nApplication.ScreenUpdating = True
\nMsgBox “An error occured.” & vbNewLine & “Please check your formula: ‘ ” & StringAction & ” ‘”, vbCritical
\nEnd Sub<\/p><\/blockquote>\n- VBA addin – this is for me not the simplest way to achieve this but it certainly the most convenient. The VBA is included in the example workbook at the bottom of this post. The added value you achieve with this is that you can add it to an addin and have it on a shortcut key.Plus I have three options with it 1 & 2 are convert all to positive or negative. This is something that you can’t achieve with the above 2 solutions. 3rd option is invert positive to negative or negative to positive (as with the above selections). Here is the code for the form:<\/li>\n
Option Explicit
\nDim RngCell\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 As Range
\nDim RngSelection\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 As Range
\nDim CellLen\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 As Long
\nDim i\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 As Long<\/p>\nPrivate Sub cmdCancel_Click()
\nUnload Me
\nEnd Sub<\/p>\nPrivate Sub cmdOK_Click()
\nApplication.ScreenUpdating = False
\nSet RngSelection = selection
\nselection.SpecialCells(xlCellTypeConstants, 1).Select
\nCellLen = selection.Cells.Count
\ni = 1
\nIf OptPositive = True Then Call ChangetoPositive
\nIf OptNegative = True Then Call ChangetoNegative
\nIf OptFlip = True Then Call Flip
\nApplication.StatusBar = False
\nApplication.ScreenUpdating = True
\nRngSelection.Select
\nMsgBox “Numbers converted”, vbInformation, “SP” & ” – Convert numbers”
\nEnd Sub<\/p>\nSub ChangetoPositive()
\nFor Each RngCell In selection
\nApplication.StatusBar = “Converting all numbers to positive :\u00a0\u00a0 ” & Int(i * 100 \/ CellLen) & “%”
\nIf IsNumeric(RngCell.Value) Then
\nIf RngCell.Value < 0 Then RngCell.Value = RngCell.Value * -1
\nEnd If
\ni = i + 1
\nNext
\nEnd Sub<\/p>\nSub ChangetoNegative()
\nFor Each RngCell In selection
\nApplication.StatusBar = “Converting all numbers to negative :\u00a0\u00a0 ” & Int(i * 100 \/ CellLen) & “%”
\nIf IsNumeric(RngCell.Value) Then
\nIf RngCell.Value > 0 Then RngCell.Value = RngCell.Value * -1
\nEnd If
\ni = i + 1
\nNext
\nEnd Sub<\/p>\nSub Flip()
\nFor Each RngCell In selection
\nApplication.StatusBar = “Converting all numbers to negative :\u00a0\u00a0 ” & Int(i * 100 \/ CellLen) & “%”
\nIf IsNumeric(RngCell.Value) Then
\nRngCell.Value = RngCell.Value * -1
\nEnd If
\ni = i + 1
\nNext
\nEnd Sub<\/p><\/blockquote>\n<\/ol>\nHere is the workbook with the above code in. Feel free to change it but if you use it please add my credit. Also if you have any other cool ways of doing this let me know. Thanks<\/p>\n
![\"\"](\"https:\/\/simoncpage.co.uk\/blog\/wp-content\/uploads\/2008\/08\/excel.gif\" "\\"excel\\"")
<\/p>\n - VBA addin – this is for me not the simplest way to achieve this but it certainly the most convenient. The VBA is included in the example workbook at the bottom of this post. The added value you achieve with this is that you can add it to an addin and have it on a shortcut key.Plus I have three options with it 1 & 2 are convert all to positive or negative. This is something that you can’t achieve with the above 2 solutions. 3rd option is invert positive to negative or negative to positive (as with the above selections). Here is the code for the form:<\/li>\n