Changing Positive Numbers To Negative

[NH]

Hello,

In column A, I have Buy or Sell (for stocks). In column B, I have the buy
or sell price. I would like to change every price in the row corresponding
with Buy to a negative number, i.e. add a "-" to the number that's already
there. Is there an easy way to do this all at once, as I have hundreds of
transactions? I tried to record a macro that would at least save me some
time doing it manually, but rather than adding a "-" to the number that's
there, it REPLACES the number with the number I added the "-" to while
recording the macro. I'm new to Excel so I hope I'm making myself clear.
Thanks for any help with this.

NH

 

[Ken Wright]

Select your data and do Data / Filter / Autofilter and filter Col A on Buy

Put -1 in an empty cell anywhere and then copy that cell using Edit / Copy

Select the data in Col B and do Edit / Go To / Visible Cells only

Do Edit / Paste Special / Tick 'Values' and 'Multiply'

Delete the -1 from the cell you put it in, and unfilter your data

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Bob Phillips]

For i = Cells(Rows.Ciount,"B").End(xlUp).Row
If Cells(i,"A").Value = "Buy" Then
Cells(i,"B").Value = - Cells(i,"B").Value
End If
Next i

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Pete_UK]

Apply autofilters and select "Buy" in column A. In a blank cell
somewhere enter -1, then click <copy>. Highlight all the values in
column B that are visible. Press F5 (Go To) then click Special and
check "Visible Cells only" - click OK. Then Edit | Paste Special |
Values (check) and Multiply (check) | OK then <esc>. Unfilter column A.

This will have changed all your Buy prices to -ve.

Pete