Solved

Remove all charaters after 2 decimal places

Posted on 2011-02-22
7
1,166 Views
Last Modified: 2012-05-11
I need to remove all numbers after 2 decimal places rounding accordingly in a specific column in vba

It would turn 27.545 to 27. 55

I need what's typed in the cell to actually look like 27.55 also since I am importing this spreadsheet after and it won't take if it's 27.545
0
Comment
Question by:techpr0
  • 4
  • 2
7 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 34956622
Something like this should do it:

ActiveSheet.Cells(2, 2).Value = Round(ActiveSheet.Cells(2, 2).Value, 2)
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34956633
For the entire column, you can use something like this.

jppinto
Dim wb As Workbook, ws As Worksheet
Dim cell As Range
 
Set wb = ActiveWorkbook
Set ws = ActiveSheet
 
For Each cell In ws.Range("B:B")
    cell.Value=Round(cell.Value,2)
Next cell

Open in new window

0
 

Author Comment

by:techpr0
ID: 34956730
jppinto,
   Your second solution worked, but how do i get it to skip row 1 ( the header row)?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 33

Accepted Solution

by:
jppinto earned 400 total points
ID: 34956793
Use this code instead...

jppinto
Sub teste()
Dim wb As Workbook, ws As Worksheet
Dim cell As Range
Dim lstrow As Long
lstrow = Cells(65536, "B").End(xlUp).Row
 
Set wb = ActiveWorkbook
Set ws = ActiveSheet
 
For Each cell In ws.Range("B1:B" & lstrow)
   cell.Value=Round(cell.Value,2)
Next cell

End Sub

Open in new window

0
 
LVL 33

Assisted Solution

by:jppinto
jppinto earned 400 total points
ID: 34956801
Sorry...typo! Change this line:

For Each cell In ws.Range("B1:B" & lstrow)

for this:

For Each cell In ws.Range("B2:B" & lstrow)
0
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 100 total points
ID: 34956818
Maybe as below...


Perhaps like this (to adapt jpp's code a little)

Dim wb As Workbook, ws As Worksheet
Dim cell As Range
Dim rng as Range
 
Set wb = ActiveWorkbook
Set ws = ActiveSheet
Set rng = ws.Range(ws.cells(2,"B"),ws.cells(ws.rows.count,"B").end(xlup))
 
For Each cell In rng
    cell=Round(cell,2)
Next cell

Open in new window

0
 
LVL 45

Expert Comment

by:patrickab
ID: 34968570
techpr0 - Thanks for the points - Patrick
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now