Solved

Remove all charaters after 2 decimal places

Posted on 2011-02-22
7
1,167 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
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.

943 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

4 Experts available now in Live!

Get 1:1 Help Now