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 :-)
------------------------------*------------------------------*----------------



"NH" <nospam@verizon.com> wrote in message
news:HOcxf.17577$ya4.11389@trnddc03...
> 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
>

[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)

"NH" <nospam@verizon.com> wrote in message
news:HOcxf.17577$ya4.11389@trnddc03...
> 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
>
>

[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