Round a range of cells using VBA

[PureFire]

Im stuck on how to round a range of cells using VBA. At the moment Im having to insert a new column, add the formula in the new column, pastespecial values to the original column then delete the new column. Which is VERY messy.

Ive tried using Application.Worksheetfunction.Round but that only allows 1 cell at a time. Is there a way to do this in VBA ?

 

[Yogi Anand]

Hi PureFire:

How about if you post some sample data that you are working with, and also what have you tried, what result did you get, and what do you think is the problem with the results you got -- and then let us take it from there.

It will be also good to know what version of EXCEL you are using!

 

[PureFire]

Im using Excel 2000.

Im trying to get the left column rounded to 4 decimal place like the right column without having to insert a new column etc.

0.5398467 0.5398
0.5346391 0.5346
0.5399591 0.5400
1.5404435 1.5404
4.1937239 4.1937

This is what I have tried so far

Application.ScreenUpdating = False
Range("A1").Select
For Col = 1 To 1
For Row = 1 To 65535
If ActiveCell.Value <> "" Then
Selection.Value = Application.WorksheetFunction.Round(ActiveCell.Value, 4)
Selection.Offset(1, 0).Select
End If
Next
Next
Range("A1").Select
Application.ScreenUpdating = True

But im sure there is a better way to do this but I cant think how.

 

[Yogi Anand]

Hi PureFire:

How about trying ...

Sub y_()
    For Each cell In [A1:A5]
        cell.Value = WorksheetFunction.Round(cell.Value, 4)
    Next cell
End Sub

Please post back if it works for you -- otherwise explain a little further -- and then let us take it from there.

 

[PureFire]

Thank you Yogi your method worked but the number of rows of data will always change. So specifying (a1:a5) wont round all cells in column A.

I tried changing the formula to read:

For Each cell In [A:A]

but it returned " runtime error '13 type mismatch " once it had finished.

 

[Yogi Anand]

Hi PureFire:

I have updated the code to cover un indefinite range in column A, but the macro will stop at the first empty cell, it encounters in column A ...

Sub y_PureFire()
    For Each cell In [A:A]
        If cell = "" Then Exit Sub
        cell.Value = WorksheetFunction.Round(cell.Value, 4)
    Next cell
End Sub

I hope this helps!

 

[PureFire]

WOW!....thank you very much for all your help and for being so fast...

It works perfectly now :D

 

[Yogi Anand]

Hi PureFire:

You Are Very Welcome -- now let us keep EXCELing!

 

[jayeshrchavda]

WOW!....thank you very much for all your help and for being so fast...

It works perfectly now :D

can anyone help me to resolve my query. I want a VBA code that works in multiple sheets. Above code is works fine but only in one sheet. I want to Round only Selected Cells and in multiple sheets.. cell address are same in all worksheets.. Pls. help....

 

[panyagak]

Hi Yogi Anand.

Just followed this thread & kudos!!

Suppose you expand data range from column F to Z, how would the code look like, please?

Regards
Patrick