Userform to roundup decimals to the nearest 0.25 or 0.5 or 0.75

[Apple08]

Hi everyone

I have to roundup an userform decimal places to the nearest 0.25 or 0.5 or 0.75. I use the following formula but it is only to set the decimal place to two digits but cannot round up. Please can anyone help?

tBox3 = Format(Round(Val(WordCount) / Range("$C$21") * 1.3, 2), "0" & Application.DecimalSeparator & "00")

Any help is appreciated.

 

[VBA Geek]

you can do it like this:

tBox3.value = worksheetfunction.MRound(myNumber,0.25)

 

[steve the fish]

you can do it like this:

tBox3.value = worksheetfunction.MRound(myNumber,0.25)

The OP wants rounding up so maybe CEILING rather than MROUND.

 

[Apple08]

Thanks, I have over 20 text boxes, do I have to list out each one to add the code?

 

[Apple08]

I have updated the code as below:

TextBox3 = Format(Round(Val(WordCount) / Range("$C$21") * 1.3, 2), "0" & Application.DecimalSeparator & "00")
TextBox3.Value = WorksheetFunction.MRound(myNumber, 0.25)

No matter I use MRound or Ceiling, the number still showing as 43.73 instead of round up to 43.75. Have I done anything wrong? And do I have to Add the same code to all of my twenty Textboxes?

 

[VBA Geek]

I have updated the code as below:

TextBox3 = Format(Round(Val(WordCount) / Range("$C$21") * 1.3, 2), "0" & Application.DecimalSeparator & "00")
TextBox3.Value = WorksheetFunction.MRound(myNumber, 0.25)

No matter I use MRound or Ceiling, the number still showing as 43.73 instead of round up to 43.75

in example MyNumber represents just the number that needs to be rounded. so you have to replace myNumber with what has to be rounded

I have over 20 text boxes, do I have to list out each one to add the code?

cant say without looking at the whole code, but in general you should need to do it for all the textboxes


and if you want to always round it up then use CEILING as suggested, depends what you want to achieve

 

[Apple08]

This is a calculation field, so the number value is changeable, is there a way to round the number up in generic?

 

[Apple08]

I have updated the code as below but the calculation field still doesn't round up. Have I done anything wrong?

TextBox3.Value = WorksheetFunction.Ceiling(Val(WordCount) / Range("$C$21") * 1.3, 0.25)

 

[VBA Geek]

why are you muliplying by 1.3?

could you post a sample of raw number and expected result?

what is Val(wordcount) and what is range(C21)?

 

[Apple08]

WordCount is an number input field so it could be various. C21 is a fix number field 913, the equation is what ever the word count, then to add 30% on top. e.g. 1000 / 913 * 1.3