VBA - Rounding Problems

[Sunjinsak]

Hi all,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Firstly, I apologise for the length of this post!
<o></o>
Secondly, I’d like to apologise for my ignorance of VBA and programming in general. I’ve never had any lessons or training and everything I’ve learned I’ve taught myself from trial and error and help files etc so please forgive me if you find my code to be inefficient, hard to follow and lacking adherence to common conventions etc. Hopefully it won’t be too bad though!
<o></o>
I’m using VBA for Excel 2003 on Windows XP.
<o></o>
I’ve designed a UserForm where a user can input a number (a monetary amount, basically) and have that number “grossed up” by a certain percentage (in this case 20%).
<o></o>
The maths behind this is simple:

result = (input * (100 / 80))

<o></o>
(Those aren’t actually the variable names I’ve declared – it’s just for illustration and, I hope, to make what I’m trying to achieve as clear as possible).
<o></o>
Now, the rule is that all results should be rounded up – so, for instance, 125.01 should be rounded up to 126 and 125.99 should also be rounded up to 126 (and the result displayed without the decimal point or it’s trailing 0s).
<o></o>
All well and good. I can do that with the following:
<o></o>

UserForm.DisplayResult.Value = Int((result) + 1)

<o></o>
As Int always rounds down I can achieve the desired result by declaring the result as an Int then adding 1.
<o></o>
This may not be the most elegant or efficient solution, but it works. However, there is an exception to the rule I mentioned earlier:
<o></o>
The result is not to be rounded up when it’s a whole number. For example 125.00 should not be rounded up, and instead just displayed as 125. The code I’m using above to display the result would round 125.00 up to 126. This is a problem!
<o></o>
What I need is a way for any result ending in .01 or above to be rounded up, but any result ending in .00 to be left alone (but displayed without the decimal point etc).
<o></o>
I attempted to achieve this with the following code. I hope you can see what I was trying to do:
<o></o>

Dim strSplit() As String
<o:p></o:p>
strSplit() = <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:City w:st="on"><st1:place w:st="on">Split</st1:place></st1:City>(result, ".")
<o:p></o:p>
If Val(strSplit(1)) = 0 Then UserForm.DisplayResult.Value = Int(result)
If Val(strSplit(1)) > 0 Then UserForm.DisplayResult.Value = Int((result) + 1)

<o></o>
I also tried the above code but declaring strSplit() as a Long instead of a String.
<o></o>
I don’t get any run-time errors – the result is calculated but always as Int((result) + 1) and never as just Int(result) – regardless of the numbers after the decimal point.
<o></o>
So, where have I gone wrong? Am I on the right tracks, or completely off the mark? Is there an easier way to do it? How can I achieve what I’m hoping to achieve?
<o></o>
Any help or advice will be greatly appreciated.
<o></o>
Thank you
Sunjin
<o></o>
<o></o>

 

[Jonmo1]

Welcome to the board..

Try using the roundup function..

UserForm.DisplayResult.Value = Application.RoundUp(result, 0)


Hope that helps.

 

[Sunjinsak]

Hi jonmo1,

Thanks for the advice and speedy reply but unfortunately that doesn't seem to work either. Despite using Application.RoundUp it appears to be rounding down.

For instance I calculated the result 125.01 (which should be rounded up to 126) and the result displayed as 125.

Sunjin

 

[Jonmo1]

You mentioned that you dimmed result as INT...
By dimming the variable as INT, it is already rounded DOWN before it even gets to the roundup function.

Change that to Double

 

[Sunjinsak]

Ah, I see. My mistake.

It worked!!

A thousand thank you's for your help.

Sunjin.

 

[Jonmo1]

Glad to help, thanks for the feedback