VBA - Rounding Problems

Sunjinsak

Board Regular
Joined
Jul 13, 2011
Messages
143
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Hi all,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Firstly, I apologise for the length of this post!
<o:p></o:p>
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:p></o:p>
I’m using VBA for Excel 2003 on Windows XP.
<o:p></o:p>
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:p></o:p>
The maths behind this is simple:
Rich (BB code):
result = (input * (100 / 80))
<o:p></o:p>
(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:p></o:p>
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:p></o:p>
All well and good. I can do that with the following:
<o:p></o:p>
Rich (BB code):
UserForm.DisplayResult.Value = Int((result) + 1)

<o:p></o:p>
As Int always rounds down I can achieve the desired result by declaring the result as an Int then adding 1.
<o:p></o:p>
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:p></o:p>
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:p></o:p>
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:p></o:p>
I attempted to achieve this with the following code. I hope you can see what I was trying to do:
<o:p></o:p>
Rich (BB code):
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:p></o:p>
I also tried the above code but declaring strSplit() as a Long instead of a String.
<o:p></o:p>
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:p></o:p>
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:p></o:p>
Any help or advice will be greatly appreciated.
<o:p></o:p>
Thank you
Sunjin
<o:p></o:p>
<o:p></o:p>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board..

Try using the roundup function..

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


Hope that helps.
 

Sunjinsak

Board Regular
Joined
Jul 13, 2011
Messages
143
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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. :confused:

Sunjin
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
Jul 13, 2011
Messages
143
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Ah, I see. My mistake.

It worked!!

A thousand thank you's for your help.

Sunjin.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,792
Messages
5,655,332
Members
418,190
Latest member
Timex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top