Vba help!

[tennisplayer]

I'm currently trying to create a rounding code in VBA.

How do I create a rounding code that allows all values in one column in the excel spreadsheet to be rounded to a specific number of places?

 

[doofusboy]

You could put something like this in Worksheet_Change. This will round all entries in Column D to 2 decimal places:

    If Target.Column = 4 Then
        Target.Value = Round(Target.Value, 2)
    End If

 

[Peter_SSs]

Possibly this? (Test in a copy of your workbook)

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Round_Column()<br>    <SPAN style="color:#00007F">Const</SPAN> Col <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "F" <SPAN style="color:#007F00">'<-- Column to round</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> Dec <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 3    <SPAN style="color:#007F00">'<-- Decimal places to round to</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Columns(Col).Offset(, 1).Insert<br>    <SPAN style="color:#00007F">With</SPAN> Range(Col & 1, Range(Col & Rows.Count).End(xlUp))<br>        .Offset(, 1).FormulaR1C1 = "=IF(ISNUMBER(RC[-1])," _<br>            & "ROUND(RC[-1]," & Dec & "),IF(RC[-1]="""","""",RC[-1]))"<br>        .Value = .Offset(, 1).Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Columns(Col).Offset(, 1).Delete<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><br></FONT>