macro to delete all digits to the right of decimal place

A

aintlifegrand79

I want to create a macro that will round a number with decimal places up and
then delete all the numbers to the right of the decimal place and the decimal
place it self. So I am hoping that it will take a cell that has
49938.523566 and turn it into 49939 but if it was 49938.4235666 it would turn
it into 49938.
 
M

Mike

maybe this
Sub test()
Const whatColumn As String = "A"
Const whatSheet As String = "Sheet1"
Dim ws As Worksheet

Dim curCell As Variant
Dim lastrow As Long
Dim iRound As Long

Set ws = Worksheets(whatSheet)
lastrow = ws.Range(whatColumn & Rows.Count).End(xlUp).Row

For Each curCell In ws.Range("A1:A" & lastrow)
iRound = Round(curCell.Value, 0)
curCell.Value = iRound
Next
End Sub
 
R

Rick Rothstein \(MVP - VB\)

Give this a try...

Sub RangeRounder()
Dim C As Range
For Each C In Selection
C.Value = Format(C.Value, "0")
Next
End Sub

It will round each entry in the selection the way you indicated you wanted.
Note that VB has a Round function, but I did not use it because I'm guessing
it doesn't work the way you would want. The Round function uses something
known as "Banker's Rounding"; it will produce the same results as the Format
function that I used EXCEPT when your number end in .5 (point-five)...
Banker's Rounding rounds this to the nearest even integer so that 1.5 and
2.5 would both round to 2... the Format function uses "normal" rounding and
would round 2.5 up to 3.

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top