PDA

View Full Version : decimal points



dandedo2
09-01-2010, 06:01 AM
how do I set up cells so that it would be, for example,

if value > 100 round to nearest whole number
if value< 100 use full number

I have 2 sets of data that are pulled into certain cells, one set is numbers under 5, say 1.2345 which I want expressed that way, whereas the other set is numbers such as 2030.8 which I want simply rounded to the nearest number.

Thank you

will1128
09-01-2010, 06:35 AM
Public sub Number_Format()
Dim ws As Worksheet
Dim c As Range

For Each ws In ActiveWorkbook.Worksheets
For Each c In ws.UsedRange
Select Case c.value
Case c.value > 100
c.NumberFormat = "0"
Case c.value < 100
c.value = c.value
Case Else
c.NumberFormat = "0.000000" 'number fo decimal places to go to
End Select
Next c
Next ws
End Sub

dandedo2
09-01-2010, 07:32 AM
thats brilliant.

where would I want to paste this so that it only impacts the top sheet in my workbook? I'm not so hot with vba, normally try to keep it to formulas/

Thank you

will1128
09-01-2010, 07:47 AM
Change

set c = Range("value":"value")

I don't know what the top sheet of yoru workbook is.

but you would need to define the range.

C = Range("A1:F5") would search through A1 to F5