Solved

Remove all charaters after 2 decimal places

Posted on 2011-02-22
7
1,170 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

717 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