Negating a Cell Using a Macro

Could this not be resolved without macro's (albeit by using a helper column) ? Something along the lines of the following formula in helper B1nn=IFERROR(-1*(A1),A1)nnWhere A1 contains the value you want to negate, and pull this down as far as you need ?nnOr am I missing the point here ?

Will these comments never end? Here is an improved version of NegateSelection5, with thanks to Tomek (see Figure 1 below) . nNote: SpecialCells(xlCellTypeConstants, xlNumbers) and UsedRange include hidden cells, nbut SpecialCells(xlCellTypeLastCell) ignores hidden cells.

Oops. In NegateSelection5 below, add the following line after "Next Cell"n    If Targ Is Nothing Then Exit Sub

The following macro has the same characteristics as NegateSelection3 in my previous comment, but this macro is almost 8-times faster when Selection contains many cells. And it has the useful result that only negated cells remain selected, so they are easily identified. For some reason, I can't post it in the usual way, so I am posting a screenshot instead (see Figure 1 below) .

If there are many cells in Selection, the following macro is almost 90 percent faster then NegateSelection3 (below) and has the useful result that only negated cells remain selected.

Sub NegateSelection5()
    Dim Cell As Range, Targ As Range, Temp As Range
    For Each Cell In Selection
        If IsNumeric(Cell.Formula) Then
            If Not IsDate(Cell) Then
                If Targ Is Nothing Then
                    Set Targ = Cell
                Else
                    Set Targ = Union(Targ, Cell)
                End If
            End If
        End If
    Next Cell
    With Selection.Parent.UsedRange
        Set Temp = Cells((.Row + .Rows.Count), 1)
    End With
    Temp.Value = -1
    Temp.Copy
    Targ.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlPasteSpecialOperationMultiply
    Application.CutCopyMode = False
    Temp.Clear
End Sub

At the risk of flogging a dead horse, here is a revision of my NegateSelection3 macro. It negates all cells in Selection that contain a numeric constant. It ignores cells with text, date, or logical (TRUE/FALSE) constants, empty cells, and all cell formulas including array formulas (CSE or dynamic). A date with or without time is ignored, but a time without date is negated (unfortunately).nnSub NegateSelection3()n    Dim Cell As Rangen    For Each Cell In Selectionn        If IsNumeric(Cell.Formula) Thenn            If Not IsDate(Cell) Thenn                Cell = -Celln            End Ifn        End Ifn    Next CellnEnd Sub

@J. Woolley:nI agree that it is simplest and actually close to what Sam asked for. nnI have a tendency to read into the questions that Allen posts in his newsletter much more than was asked, then answer the expanded questions. That's where the macro to keep the formula came from.nnThe simplest solutions are usually best, that should be my motto, but it's hard to keep your own advice.

@TomkenAll things considered, I think my NegateSelection3 macro is best. See 2021-11-06 11:10:54 below.

Here is a macro that combines J. Wooley's ideas and mine.nPlease read our previous comments to understand what it does.n-----------------------------------------------------------nPublic Sub PasteNegate2()n Dim rTemp As Rangen On Error GoTo errHandlern n ActiveSheet.UsedRange 'resets used rangen Set rTemp = Cells.SpecialCells(xlLastCell).Offset(1, 1)n 'cell in next row and column after all data.n rTemp = -1n rTemp.Copyn Selection.PasteSpecial Paste:=xlPasteValues, _n Operation:=xlPasteSpecialOperationMultiplyn Application.CutCopyMode = Falsen n rTemp.Delete Shift:=xlToLeftn 'rTemp.Clear 'is not good enough to reset used range!n Set rTemp = Nothingn ActiveSheet.UsedRangen n 'optional cleanupn For Each Cel In Selectionn If Left(Cel.Formula, 3) = "=((" And Right(Cel.Formula, 8) = ")*-1)*-1" Thenn Cel.Formula = "=" & Mid(Cel.Formula, 4, Len(Cel.Formula) - 11)n End Ifn Next CelnExit SubnnerrHandler:n If Err.Number = 1004 Thenn MsgBox "One of celected cells contains array formula. " & _n "Please deselect it."n Elsen MsgBox "One of the selected cells cannot be processed. " & _n "Try selecting cells one by one to identify which cannot be processed."n End Ifn Resume NextnnEnd Sub

@J. Wooley nRe: indenting, I also figured out to use non-breaking spaces and once the code was ready, used Ctrl+H to replace four regular spaced with for with 4 non-breaking spaces in word, then copy paste.nAnother option I used is to replace four regular spaces with four (or more) html codes for nonbreaking space (& nbsp;). nisn't there an easier way?n&  Tomek

@TomeknRe. indenting, see David Bonin's 2021-01-18 comment here:nhttps://excelribbon.tips.net/T011598_Limiting_Who_Can_Delete_Data.htmlnI use Ctrl+H in Notepad to replace 4 leading space chars with 4 Alt+0160 non-breaking space chars or with 2-pair of 1 non-breaking space plus 1 standard space. You can also use Ctrl+Shift+Space in Word for a non-breaking space char, which is visible when toggled with Ctrl+Shift+*.

@J. WooleynI tried creating a range named Negative1 but instead of referring it to a cell, I tried referring it to =-1. The range was created, but it couldn't be used in the set statement nor in the copy statement. nnAny thoughts on this?

@J. WooleynI just noticed that you too created programmatically a range before copying it to the clipboard. ;-)

@J. WooleynI did not see your comment while composing mine. My approach is very similar, only I couldn't figure out how to place -1 in the clipboard without copying an actual cell.nI added some extra code to cleanup expanding formula and also to prevent run-time error when any of the cells contain array formula. One such formula is =CELL(info, ref). It does not look like an array but Excel considers it to be. Your code suffers from this too. I suggest we try to combine our codes in one.nnI have another question for you: How did you achieve indented code in the comment. I managed to get it too, but had to jump through some hoops. To take it off line, please respond by e-mail to me, my address is unhidden.

All of the macros give by Allen will work fine with just numbers and with formulas resulting in the number. However the result will be negated numbers in the selected cells and any formulas in those cells will be gone. nTo keep the formulas and just change the sign of the results there is a very easy way: copy a cell containing -1 into clipboard and use Paste Special Values with Operation Multiply into the target cells. The macro that does this is shown below.nTo use the macro, you need to place -1 in a cell that will not interfere with your data and create a named range "Negative1" referring to this cell. My suggestion is to select a cell in the top row of the spreadsheet, name it, then hide Row 1 to minimize a chance of inadvertent changes to this cell. The macro will target all cells in current selection, so you may change signs of several cells at once, similarly to the macros given in the tip. nThe macro will negate the explicit numbers, multiply any formulas that return a number by -1, and will ignore any cells that contain plain text. If the formula returned text or empty string the cell will display #VALUE!. There is no such thing as -text. This applies to simple formulas like =22 or =A1, as well as to formulas containing nested functions and even logical tests (IF formulas). nThe purpose of the second part of the macro is to do some cleanup of what otherwise would clutter the formula cell(s), which the macro modified. Each paste special operation adds a bracket at the beginning of the formula, another one at the end and follows it with "*-1". After several Paste Special operations the formula might look like this: =((((((((((((B6)*-1)*-1)*-1)*-1)*-1)*-1)*-1)*-1)*-1)*-1)*-1)*-1. Once the operation was <b>carried out twice</b>, the for-next loop will remove two starting brackets and ")*-1)*-1" from the end, returning the formulas in all cells in the selection to the original ones. After all, multiplying by -1 twice is kind of nul operation. As an added benefit, the formulas that did not return a number will also be reverted to originals, avoiding the #VALUE! errors.nThe Paste Special operation will generate an error if any cell in the selection contains an array formula. This error is trapped in the macro; a message will be displayed and the contents of the cells will not be changed.nBlank cells are treated as zeros and changed to 0 (0*-1 = 0). To avoid this, just do not select blank cells before running the macro. It is not worth adding code to the macro to exclude blank cells, but it could be done.nHere is the macro:nPublic Sub PasteNegate()n'multiplies formula in each cell of the selection by the formula in range "Negative1"n'Negative1 is intended to have -1 value in itnOn Error GoTo errHandlernn        Range("Negative1").Copyn        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiplyn        Application.CutCopyMode = Falsen        n        'optional cleanupn        For Each Cel In Selectionn        If Left(Cel.Formula, 3) = "=((" And Right(Cel.Formula, 8) = ")*-1)*-1" Thenn                        Cel.Formula = "=" & Mid(Cel.Formula, 4, Len(Cel.Formula) - 11)n                End Ifn        Next CelnExit SubnnerrHandler:nIf Err.Number = 1004 Thenn        MsgBox "One of celected cells contains array formula. Please deselect it."nElsen        MsgBox "One of the selected cells cannot be processed. Try selecting cells one by one to identify which cannot be processed."nEnd IfnApplication.CutCopyMode = FalsennEnd Sub

If there are many cells in Selection, this macro is fast. It will negate formulas as well as constants. It will not disturb text constants, but empty cells will become zero and formulas that returned text will return #VALUE! instead:nnSub NegateSelection4()n    Dim Temp As Rangen    With ActiveSheet.UsedRangen        Set Temp = Cells((.Row + .Rows.Count), 1)n    End Withn    Temp.Value = -1n    Temp.Copyn    Selection.PasteSpecial Paste:=xlPasteValues, _n        Operation:=xlPasteSpecialOperationMultiplyn    Application.CutCopyMode = Falsen    Temp.ClearnEnd SubnnSee https://sites.google.com/view/MyExcelToolbox/

@TomeknWilly's macro works fine if Selection contains only numeric values.

Willy's macro is very clever, but not if Selection contains an empty or non-numeric cell.nWilly's macro and the Tip's macros convert numeric formulas into negated constants, which might not be intended.nThis macro avoids those problems:nnSub NegateSelection3()n    Dim rCell As Rangen    For Each rCell In Selectionn        If IsNumeric(rCell.Formula) Thenn            rCell = -rCelln        End Ifn    Next rCellnEnd SubnnSee https://sites.google.com/view/MyExcelToolbox/

Just a note that IsNumeric VBA function is slightly different from the worksheet function ISNUMBER.n    ·IsNumeric returns TRUE for Boolean TRUE and FALSE while ISNUMBER returns false.n    ·IsNumeric returns False for numbers formatted as a date but not as just time! ISNUMBER returns TRUE for both.n    ·For blank cells IsNumeric returns TRUE, but ISNUMBER returns FALSE.

@willy Vanhaelen:nYour macro does not work. It results in value of FALSE being put in every selected cell!

Here is a one-liner for the first macro avoiding a loop:nnSub NegateSelection1()nSelection = Evaluate(Replace("IF(AND(ISNUMBER(#),#<>0),-#)", "#", Selection.Address))nEnd Sub