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 B1

=IFERROR(-1*(A1),A1)

Where A1 contains the value you want to negate, and pull this down as far as you need ?

Or 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) .
Note: SpecialCells(xlCellTypeConstants, xlNumbers) and UsedRange include hidden cells,
but SpecialCells(xlCellTypeLastCell) ignores hidden cells.

Oops. In NegateSelection5 below, add the following line after "Next Cell"
    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.ClearEnd 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).

Sub NegateSelection3()
    Dim Cell As Range
    For Each Cell In Selection
        If IsNumeric(Cell.Formula) Then
            If Not IsDate(Cell) Then
                Cell = -Cell
            End If
        End If
    Next Cell
End Sub

@J. Woolley:
I agree that it is simplest and actually close to what Sam asked for.

I 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.

The simplest solutions are usually best, that should be my motto, but it's hard to keep your own advice.

@Tomke
All 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.
Please read our previous comments to understand what it does.
-----------------------------------------------------------
Public Sub PasteNegate2()
Dim rTemp As Range
On Error GoTo errHandler

ActiveSheet.UsedRange 'resets used range
Set rTemp = Cells.SpecialCells(xlLastCell).Offset(1, 1)
'cell in next row and column after all data.
rTemp = -1
rTemp.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationMultiply
Application.CutCopyMode = False

rTemp.Delete Shift:=xlToLeft
'rTemp.Clear 'is not good enough to reset used range!
Set rTemp = Nothing
ActiveSheet.UsedRange

'optional cleanup
For Each Cel In Selection
If Left(Cel.Formula, 3) = "=((" And Right(Cel.Formula, 8) = ")*-1)*-1" Then
Cel.Formula = "=" & Mid(Cel.Formula, 4, Len(Cel.Formula) - 11)
End If
Next Cel
Exit Sub

errHandler:
If Err.Number = 1004 Then
MsgBox "One of celected cells contains array formula. " & _
"Please deselect it."
Else
MsgBox "One of the selected cells cannot be processed. " & _
"Try selecting cells one by one to identify which cannot be processed."
End If
Resume Next

End Sub

@J. Wooley
Re: 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.
Another option I used is to replace four regular spaces with four (or more) html codes for nonbreaking space (& nbsp;).
isn't there an easier way?
&  Tomek

@Tomek
Re. indenting, see David Bonin's 2021-01-18 comment here:
https://excelribbon.tips.net/T011598_Limiting_Who_Can_Delete_Data.html
I 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. Wooley
I 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.

Any thoughts on this?

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

@J. Wooley
I 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.
I 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.

I 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.
To 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.
To 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.
The 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).
The 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.
The 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.
Blank 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.
Here is the macro:
Public Sub PasteNegate()
'multiplies formula in each cell of the selection by the formula in range "Negative1"
'Negative1 is intended to have -1 value in it
On Error GoTo errHandler

        Range("Negative1").Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
        Application.CutCopyMode = False
        
        'optional cleanup
        For Each Cel In Selection
        If Left(Cel.Formula, 3) = "=((" And Right(Cel.Formula, 8) = ")*-1)*-1" Then
                        Cel.Formula = "=" & Mid(Cel.Formula, 4, Len(Cel.Formula) - 11)
                End If
        Next Cel
Exit Sub

errHandler:
If Err.Number = 1004 Then
        MsgBox "One of celected cells contains array formula. Please deselect it."
Else
        MsgBox "One of the selected cells cannot be processed. Try selecting cells one by one to identify which cannot be processed."
End If
Application.CutCopyMode = False

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

Sub NegateSelection4()
    Dim Temp As Range
    With ActiveSheet.UsedRange
        Set Temp = Cells((.Row + .Rows.Count), 1)
    End With
    Temp.Value = -1
    Temp.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlPasteSpecialOperationMultiply
    Application.CutCopyMode = False
    Temp.Clear
End Sub

See https://sites.google.com/view/MyExcelToolbox/

@Tomek
Willy'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.
Willy's macro and the Tip's macros convert numeric formulas into negated constants, which might not be intended.
This macro avoids those problems:

Sub NegateSelection3()
    Dim rCell As Range
    For Each rCell In Selection
        If IsNumeric(rCell.Formula) Then
            rCell = -rCell
        End If
    Next rCell
End Sub

See https://sites.google.com/view/MyExcelToolbox/

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

@willy Vanhaelen:
Your 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:

Sub NegateSelection1()
Selection = Evaluate(Replace("IF(AND(ISNUMBER(#),#<>0),-#)", "#", Selection.Address))
End Sub