Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Add ( ) to cell contents

  Asked By: Cesar    Date: Sep 21    Category: MS Office    Views: 1077
  

I am attempting to write a macro that will compare the contents of
one cell
with the contents of the one below it. If the contents are
different, I want
to place parentheses around the value in the bottom cell, if they
are the
same I want the contents of the bottom cell to be deleted.

The macro I wrote does exactly what I want it to do, except instead
of adding
the ( ) it adds a negative sign - . Here is the line that I assume
needs
changing:


ActiveCell.Offset(1, 0).Value = "(" & ActiveCell.Offset(1, 0).Value
& ")"

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Chaths Massri     Answered On: Sep 21

If you are dealing with numbers, it is a cell  formatting issue.
Excel treats numbers in parens as negatives. if you change to the
accounting category, remove the dollar sign, you will return to a
number in parens.

 
Answer #2    Answered By: Tarron Thompson     Answered On: Sep 21

For this to have happened, the contents  are numeric.
If you're wanting the "()" to show, you have two choices.
change the formatting of the cells to use () for negative  numbers:
"0.00_);(0.00)"
or change it to a text string.
which will cause you problems if you're trying to use add  the numbers.
If you're wanting to change the contents to text (to essentially
remove them from the calculations) use:

ActiveCell.Offset(1, 0).Value = "'(" & ActiveCell.Offset(1, 0).Value
& ")"
(note added single quote)

 
Answer #3    Answered By: Vid Fischer     Answered On: Sep 21

this got me closer. Unfortunately I lose the decimal
precision of the original value. For example, say the value in the
cell officially was 3.1412563, but I had the cell  rounded to 3.1.
After running the macro  with a tweak one way or the other I end up
with (3) or (3.1412563).

It's like I need to capture not the specific value of the cell, but
the displayed value.

 
Answer #4    Answered By: Daniel Costa     Answered On: Sep 21

Back to my original question.
Are you wanting to retain the numeric value?
or are you content with converting it to text?

Also, are all the values being displayed at the same
precision? (decimals to 1,2 or 3 places) or are they different?

 
Answer #5    Answered By: Grace Ellis     Answered On: Sep 21

I guess what I'm getting at is if you're wanting all of the values
displayed as,
say 2 place  decimals, you can use something like:

Cells(i, 5) = "'(" & Format(Cells(i, 1), "##,##0.00") & ")"

the result will be a text string.
If you're wanting the value to be forced to round down (or up, or truncate)
you'll have to do that to the cells(i,1) within the formula.

does this help?

 
Answer #6    Answered By: Alisha Johnson     Answered On: Sep 21

Once we thought to use a round function we got it done. Thanks for
the help. I'm posting the entire script below in case anyone else
comes along trying to do this.

Selecting the top left corner of a table, it goes through and
compares each cell  to the one below it, deleting the matches and
placing the differences into parentheses rounded to one decimal
point.

Sub DataCompareDecimal()
Dim x As Integer

Do While ActiveCell.Value <> ""

Do While ActiveCell.Value <> ""
x = x + 1
If ActiveCell.Offset(1, 0) = ActiveCell.Value Then

ActiveCell.Offset(1, 0).Value = ""
ActiveCell.Offset(0, 1).Activate


Else
ActiveCell.Offset(1, 0).Value = "'" & "(" & Round
(ActiveCell.Offset(1, 0).Value, 1) & ")"
ActiveCell.Offset(0, 1).Activate

End If

Loop

ActiveCell.Offset(2, -x).Activate
x = 0

Loop

End Sub

 
Didn't find what you were looking for? Find more on Add ( ) to cell contents Or get search suggestion and latest updates.




Tagged: