macro to convert negative values with Brackets

[howard]

I have values and percentages in Col D:J on sheets 16 to 25

I would like a macro to convert the negative numbers only eg -125, -128,965 etc in these columns and on these sheets with a bracket eg (125), (125,965) etc


Your assistance in this regard is most appreciated

 

[steve the fish]

Is the cell to keep its negative value? Id presume so. Use formatting eg:

#,##0.00;(#,##0.00)

 

[Misca]

Sounds like what you're really after could be number formatting. Putting your values inside brackets would turn your number into text.

You can google for Custom number formats or search for tutorials in YouTube.

 

[howard]

the cell must keep its negative value.

If there were only values in these columns it would be easy to format these using #,##0.00;(#,##0.00)


However, there are also percentages in these columns (eg 8.58%), so I guess I would need a macro to format -values with eg -250.00 with (250), ignoring percentages

 

[steve the fish]

It sounds like a confused sheet to me! Why does a column have a mixture of cell types? You just need to format them as appropriate. There is the format painter to use?

 

[howard]

Managed to sort this out by changing the regional settings

 

[Rick Rothstein]

However, there are also percentages in these columns (eg 8.58%), so I guess I would need a macro to format -values with eg -250.00 with (250), ignoring percentages

Do you have any negative percentages and, if so, do negatives also get changed to parentheses as well?

 

[howard]

Hi Rick

Thanks for the reply

I have no negative percentages. If this does occur the % must be reflected with minus sign for e.g. -10.15%

 

[Rick Rothstein]

I have no negative percentages. If this does occur the % must be reflected with minus sign for e.g. -10.15%

The following Custom Format cannot handle negative percentages (they would be converted to negative non-percentage numbers). So, if your "no negative" is always true, try this Custom Format...

[<0](#,##0.00);[<1]0.00%;#,##0.00

Note: This Custom Format assume you will never have positive non-percentage numbers less than one in a cell (they would be converted to percentages if you did).

 

[howard]

Thanks Rick. It works perfectly