Rounding a range of cells in VBA

[daveus]

I searched and couldn't make anything work that i found on rounding a range of cells. I have a range selected:

Range(Cells(therow, thecolumn), Cells(RowEnd, thecolumn + 2)).Select

I changed the number format like this:

Selection.NumberFormat = "0.00"

However, when i write to a text file, all the decimal places come back. How can i round the selected cells to the above "0.00" format in VBA. Thanks all!

 

[Tazguy37]

Before you write to a text file, try copying the cell(s) to someplace else and PasteSpecial them with Values and Formats. Does that do it?

 

[daveus]

I'm not sure how to do that...I am actually pasting the range from another location, so if I can paste them with format, etc. in VBA, that would be great. If you would elaborate, i would appreciate it. Thanks!

 

[Tazguy37]

Instead of using .Paste, use .PasteSpecial

There's more in the VBA help under "PasteSpecial Method (Range Object)". Here's an example from the forum, too.

 

[Tom Urtis]

Formatting only affects the look of the value. Its underlying true value remains unchanged and that is why your text file is doing what it is doing.

After this line:
Range(Cells(therow, thecolumn), Cells(RowEnd, thecolumn + 2)).Select


Add this line:
Selection.Value = Selection.Text


Edit - - the code line assumes you already rounded the values earlier in your macro as you implied.

 

[KenWright]

Formatting will only affect how the number is displayed. Take a look at the Round function in VBA, eg

Sub rounding()

Dim LCol As Long
Dim ColNum As Long
Dim rng As Range
Dim cel As Range

LCol = ActiveSheet.UsedRange.Column - 1 + _
        ActiveSheet.UsedRange.Columns.Count

For ColNum = 1 To LCol
On Error Resume Next
   Set rng = Intersect(ActiveSheet.UsedRange, Columns(ColNum), _
       Cells.SpecialCells(xlCellTypeConstants, 23))
   For Each cel In rng
       cel.Value = Round(cel.Value, 2)
    Next cel
Next ColNum

End Sub

 

[Robb]

Rounding Function in VBA

Does this rounding funtion work in Excel '97?

I used this code and it worked in 2000, but I received an error using '97.