Round up to next whole number

[Todd Huttenstine]

numcount = Application.WorksheetFunction.Count(Worksheets
(1).Columns("B:B")) * 0.1

How do I round the above variable up to the next whole
number? For instance currently this code is calculating
to 7.1. I need it to show 8.


Thank you
Todd Huttenstine

 

[Frank Kabel]

Hi Todd
add the line
numcount=ROUND(numcount,0)

 

[Tom Ogilvy]

numcount = Application.WorksheetFunction.Count( _
Worksheets(1).Columns("B:B")) * 0.1
numcount = application.RoundUp(numcount,0)

 

[JE McGimpsey]

One way:

With Application.WorksheetFunction
numcount = .Ceiling(.Count(Worksheets(1).Columns(2)) * 0.1, 1)
End With

 

[Bernie Deitrick]

Todd,

NumCount = Int(NumCount) +1

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Todd,

Ignore my other post - Brain freeze.

NumCount = Application.RoundUp(NumCount, 0)

HTH,
Bernie
MS Excel MVP

 

[Frank Kabel]

Hi
make this
numcount=application.ROUNDUP(numcount,0)

 

[kkknie]

Try this.

If CInt(numcount) <> numcount Then numcount = CInt(numcount + 1)

Basically add 1 and convert to integer if it is not an intege
already.

 

[Todd Htutenstine]

Thanks to everyone who replyed.

 

[Dana DeLouis]

Just to add. Depending on the test of course, the following can be about 10
times faster. (or more)

n = 7.1

ans = Int(n) - (n <> Int(n))
'vs
ans = WorksheetFunction.RoundUp(n, 0)

 

[Dana DeLouis]

Just for discussion. Because you are doing a multiplication before rounding
up, you may want to consider thinking about a degree of accuracy in your
answer. It may not apply in your case though.
Your multiplication may return an answer like 7.000000001. Do you wish to
consider this 7, or literally, and round it up to 8? This is where you
have to decide.

Sub xxx()
Dim n

'// Maybe not what you wanted
n = 7.000000000001
n = Int(n) - (n <> Int(n)) ' -> 8

n = 7.000000000001
n = Round(n, 10) ' Your acceptance level here ->7
n = Int(n) - (n <> Int(n)) ' -> 7
End Sub

Just something to think about, that's all. :>)