PasteSpecial value*-1

[ndendrinos]

I'm trying through a VBA IF code to do this:

……..A…….........B…….
54...............................
55..................49……..
56.................-50……..
57…….......……...-1………..

to that:

……..A…….........B…….
54...............................
55..................49……..
56.................-49……..
57…….......……....0………..

[B55] is a linked cell
[B56] is a number (not typed by hand but introduced through code)
[B57] is the sum of [B55:B56]

How can I get this result based on this logic:
For each negative cell in Row 57
Go up two rows/same column & copy value
Come down one row/same column and paste special Values *-1

(it is the business of the "*-1" that I need most help with if possible that is)

Many thanks

 

[njimack]

Something like this:

Sub test()
Dim r As Range
Dim v As Variant

For Each r In Selection


    If Left(r.Formula, 4) = "=SUM" Then Cells(r.Row - 1, r.Column) = -Cells(r.Row - 2, r.Column).Value
    
Next r
End Sub

 

[ndendrinos]

great solution njimack sure beats my original recording of:

Range("E58").Activate
    ActiveCell.FormulaR1C1 = "=R[-3]C*-1"
    Range("E58").Activate
    Selection.Cut
    Range("E56").Activate
    ActiveSheet.Paste

Many thanks