?
Solved

Remove all charaters after 2 decimal places

Posted on 2011-02-22
7
Medium Priority
?
1,172 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Accepted Solution

by:
jppinto earned 1600 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 1600 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 400 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

777 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