Macro for Changing Sign

[bkanne]

Can someone please help me write a macro that will change the sign of the actively selected cells, even if a formula is in them already?

This works well for hardcoded figures:

Sub ConvertValues()
Dim rng As Range
    Dim area As Range
    Dim c As Range


    Set rng = Selection
   
    For Each area In rng.Areas
        For Each c In area.Cells
            c.Value = c.Value * (-1)
        Next c
    Next area
End Sub

But, if applied to a formula, it will hardcode it, which I'm trying to avoid.

I had the idea of using something that perhaps inserts "=-" at the beginning of all actively selected cells and then would toggle back on a second run of the code and remove the negative, but need some help writing this / not sure if it is the best way.

Please let me know if you have any ideas / can help here. Thanks so much!

 

[NARAYANK991]

Hi ,

Try this :

Sub ConvertValues()
    Dim rng As Range, area As Range, c As Range
    
    Set rng = Selection
   
    For Each area In rng.Areas
        For Each c In area.Cells
            With c
                 If .Formula Like "=" & "*" Then
                    .Formula = "=" & "(" & Mid(.Formula, 2) & ")" & " * (-1)"
                 Else
                    If IsNumeric(.Value) Then
                       .Value = Application.Evaluate("=" & "(-1) * " & c.Value)
                    End If
                 End If
            End With
        Next c
    Next area
End Sub

But be aware that this might not convert all positive values to negative values.

For example , suppose A1 and A2 contain 17 and 33 respectively ; now in A3 , you have a formula :

=SUM(A1 , A2) or =A1 + A2

Thus , before you run the macro , A3 will contain 50.

When the macro is run , it will convert the values in A1 and A2 to -17 and -33 , and you might expect the formula output in A3 to be -50 ; however , because the macro is changing the sign of the formula also , the introduction of a -1 in the formula will result in A3 containing 50 , as before.

Narayan

 

[bkanne]

Thank you! Works great and I appreciate the detailed example. Very clear.

Thanks again!