Converting Positive Values to Negative numbers, and vice-versa

[cubs610]

Hi Everyone,


Question- I have a spreadsheet where I need to convert all the positive numbers, to negative numbers. Likewise the negative numbers need to be converted into positive... this is for the whole sheet, not just individual cells. Granted I can do the paste special, multiply trick, but this is too inefficient. I'm thinking I may need to create a macro.... any thoughts?

 

[kaushik03]

Hi cubs610,


You can use ABS function to convert negative numbers to positive


=ABS(reference cell)


Regarding, converting the positive numbers to negative numbers, what I can quickly think of is mutiplying the number with -1


If you could share your need to so this job, we could probably come up with an alternative approach to do this.


Kaushik

 

[Montrey]

My only guess is if statement, since you need both switches.

 

[Colin Legg]

A starting point on the VBA code would be:

Sub foo()


    Dim rngTarget As Range

Dim rngCell As Range


    On Error Resume Next

Set rngTarget = Sheet1.UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers)

On Error GoTo 0


    If Not rngTarget Is Nothing Then

For Each rngCell In rngTarget.Cells

rngCell.Value2 = -rngCell.Value2

Next rngCell

End If


End Sub

Once you're happy with it (change sheet1 to the codename of the target sheet) you can make it run faster by turning off screenupdating, setting calculations to manual and (if necessary) disabling events.


Note: I have assumed there are no dates on the worksheet.


However, I like your suggestion of using copy (-1) > select target cells > pastespecial > multiply. Is the pastespecial operation really that slow for you?

 

[cubs610]

Hello All...


Thank you for your responses, see below.


@kaushik03-- I neglected to mention that I am familiar with the ABS function (sorry), but thank you for your help and the reminder. I appreciate it To answer your question I need to convert a large sheet of numbers all at once.


@Montrey-- hmmm.... didn't think of that solution. Do you have an idea of what the formula would look like?


@Colin- thanks for the code. And to answer your question, sadly yes, the paste special operation is that slow (actually tedious) since a large number of cells (think hundreds to thousands) need to be converted. Selecting the cells takes too much time and the paste special is a "one way street", meanining it only converts to positive, not negative. Which is where I'm thinking the built in functions for Excel are too limited and code may be the only way..... although I hope that is not the case. Does this help?

 

[Colin Legg]

Hi cubs,


Okay, well if it is that slow then give the code I posted a try. If you're not sure how to add the further optimisations I suggested then let me know and I'll put them in for you.


I don't quite follow the "one way street" point you made though - if you multiply all numbers by -1 then that should convert negative to positive and postive to negative, shouldn't it? (It does for me).

 

[SirJB7]

@Colin Legg

Hi!

Nice code. You could perform a check for non numeric (boolean, dates & times, text) cell values with the proper "Application.WorksheetFunction.IsXXXX" functions.

Regards!

 

[Debraj]

Hi cubs610,


* Write -1 in a separate blank cell..

* Copy the cell.

* Select all cells..

* Select Special Cells (Alt E + G + S + Costant / Number) for 1st level security

* Go to Paste Special and Paste as Multiply (Alt + E S M)

* For 2nd Level security.. You can use "Skip Blanks" too..

It will automatically overlooked..

Blank Cells, Non-Numeric Value and will paste the value in same location, without using any extra column / VBA


Regards,

Deb

 

[cubs610]

Hi Guys,


Thanks for all your help.. I truly appreciate it.


@Colin- I'll give the paste special another go and see what happens. Odds are I screwed something up when I tried it -


I'll give the above solutions a try. Have a great day everyone.


Thanks again!