Formating of 2 decimal places in macro

J

JohnD0e

New Member
Joined
Feb 24, 2010
Messages
2
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Thanks to a primer from PGC01 regarding some fun use of the EVALUATE function, and a tip from DonkeyOte that it existed, this is my loopless macro solution...

Select a range of cells, then run one of these two macros:
Code: 
Sub RoundSelection()
'Select a range and then run the macro
Dim Cell As Range

    With Selection.Cells
        .Value = Evaluate("IF(ROW(1:" & Selection.Cells.Count & "),ROUND(" & .Address & ",2))")
    End With

End Sub


Sub TruncateSelection()
'Select a range and then run the macro
Dim Cell As Range

    With Selection.Cells
        .Value = Evaluate("IF(ROW(1:" & Selection.Cells.Count & "),TRUNC(" & .Address & ",2))")
    End With

End Sub
 
Upvote 0
Sorry about the cross post but thank you for the ideas I will test some of these out when I get in to work tonight.

nightcrawler23, I had been using some thing a bit like you posted but yes i wanted to some how create a VBA or button for it as it a issue that I was facing it seams hits me each day so I am thinking of a way to try and make my life a bit simpler.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Change a decimal place to a comma
Replies
4
Views
403
Roo2021
R
JohnZ1156
  • Solved
Formula adding 7 decimal places to result
Replies
3
Views
318
Joe4
Joe4
M
  • Question
Excel Chart - number formatting as decimal only if decimal values are available in source data
Replies
0
Views
569
manekankit
M
D
  • Question
FILTER Function Doesn't Display Formatting
Replies
3
Views
106
Fluff
Fluff
B
  • Question
Conditional formatting the number format of a chart
Replies
2
Views
493
Brasil
B

Forum statistics

Threads
1,215,335
Messages
6,124,326
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top