?
Solved

Remove all charaters after 2 decimal places

Posted on 2011-02-22
7
Medium Priority
?
1,175 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

850 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