VBA Code to Multiply by -1

[allemanable]

Hi everyone - I know I can use paste special values to multiply by -1 to change the sign, but I am trying to write a vba script that will do it for me. My file has sales data, but treats returns as a positive number. I need the script to find all "returns" line items and multiply the quantity and the sales dollars by negative 1.

This is only a subset of the the data. There are thousands of line items and the type may contain returns or sales.

 

[joeu2004]

Is this what you are looking for?

Sub doit()
Dim v As Variant, i As Long
v = Selection
For i = 1 To UBound(v, 1)
    If LCase(v(i, 2)) = "returns" And v(i, 1) > 0 Then v(i, 1) = -v(i, 1)
Next
Selection = v
MsgBox "done"
End Sub

Select the data in columns labeled "Sales Values" and "Returns" before executing the macro.

 

[Rick Rothstein]

Here is another macro that you can consider...

Sub MakeReturnsNegative()
  With Range("B2", Cells(Rows.Count, "B").End(xlUp))
    .Value = Evaluate("IF(" & .Offset(, 1).Address & "=""Returns"",-1,1)*" & .Address)
  End With
End Sub