formatting of a cell to 2 decimal places

So here's the problem.
if I change the formatting of a cell to 2 decimal places, it appears as two decimal places (as it should) for example $88.88888 will show as $88.88

However, when I use this data in another application that displays this data it will display as 88.88888 still. I need to actually take the value and truncate it to 88.88 eg 8.8888888 will become 8.88

I have been using trunc by hand and wanted to try and find out if there is a way that I could write some sort of macro to do this for me each time.

Any help on the subject would be great as VBA is still new to me

for example $88.88888 will show as $88.88

It would display as $88.89

If the value is a resuly of a formula, then you can integrate runding into the formula:

=ROUND(my formula except for the = sign, 2)

Put this macro in a standard module.

Select the cells to truncate and then run the macro...

Please Login or Register  to view this content.

Are you sure you want to truncate and not round?

This version seems to work without looping:

Please Login or Register  to view this content.

If you want to round instead, that's a simple tweak:

Please Login or Register  to view this content.

How about this:

=INT(B2*100)/100

or this:

=ROUNDDOWN(B1;2)

Edit: But I obviously misunderstand issue :D

Sorry, the codes above were tweaked...here are the two again...make a selection of cells, then run the macro of choice:

Please Login or Register  to view this content.

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

http://www.mrexcel.com/forum/showthread.php?t=450611

Well at my second job for the day so now at last have time to have a play with the examples

I would love to go with the Truncate method if I ran things but I think the Round (up) method is what a company would want to with having though about it today :-(


JBeaucaire went with your code as it was pretty much ready to run. One thing I know users tend to do is that rather than select a range they will select the whole column.

I have tried to fix this by adding the following code block by doing a "if not" statement it works but is this the correct way to not run this code on blank cells ?

Please Login or Register  to view this content.

The only other issue I can see is that first record is a header record what is the correct method to skip the first record. I was thinking to do a check to see if the cell had a number value in it but I don't think that's best way as some times the header might be a number.

Please Login or Register  to view this content.

Here you go, this will work even if they select the entire column:

Please Login or Register  to view this content.

If that takes care of your need, use the Thread Tools menu to mark the this thread as [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

Click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.