Free Microsoft Excel 2013 Quick Reference

Round up/down - VBA code request


I would like to know how to set up Excel VBA code to limit an user from inputting something that is not divisible by 50. For example, I am setting up an order form for colleagues to order some educational fact sheets. These sheets are printed in packs of 50 - so they would have to order quantities in numbers divisible by 50. I would like to inform them to order in multiples of 50 only * AND * automatically round anything they input EITHER up or down by 50. That is, if they enter 418 it rounds down to 400, if they enter 469 it rounds up to 500.

I am hoping that this formula will automatically round based on the user's input. I have several rows with 5 cells per row that users can input quantities into. I want to be able to round all of these rows.

Thank you so very much!

Post your answer or comment

comments powered by Disqus
Hello, I am using a formula to get the exact business hours between two dates and time. However, it doesn't calculate "business hours" properly when the event STARTS before or after the business hours. For example: I have a start date and end date below and the business hours are 8AM to 5PM, it should calculate the elapsed time = 2:13 (2 hours and 13 minutes), since the business hours started at 8AM. it should not count something that started at 1AM, before the business hours. Make sense? If the event started and ended within the business hours then it works fine.
2/11/2011 1:00
2/11/2011 10:13

How can I make the formula to take business hours into consideration when it STARTS outside of business hours?

Maybe I could round up/down the start / end column to to tnearest business hours too with a conditional IF statement? Any help?

Is it possible to ask excel to round up/down values?
For example:
0.7834 for 0.80
0.6221 for 0.60
0.65 for 0.70

Hi Everyone,

New guy here :o)

When I calculate a figure It generally has about 3 to 4 rounding numbers example :1.7897 so I use the rounding hot keys to take it to a monetary value of 1.79.

When I copy & paste value to another sheet ie what I want the client to see........ I need it to stay as 1.79 sometimes it does but if you click on the cell you can still round up and down I don't want it to do this!!!! as this is what the client will do and my end result is always different to his.

On short exercises I just manually punch in the figure after rounding however when I have pages & pages its very time consuming & the chance of making a mistake is huge.

Please see my example attached the client copy rates (highlighted) these can still be rounded up & down from my calculations that's my problem.

Hope you can help.


Dear All,

I am doing VBA Programming in Excel 2008, i am facing rounding issue in VBA coding, some times its taking rounding up and some times it is taking rounding down, please advise if any one have come across.



Thanks in advance


Can anyone help me with working out a formula which automatically rounds data
up or down?

I am developing an assessment data base for my school and need average
scroes rounding up/down to the nearest .0, .3 or .7

Any help would be greatly appreciated.

Thank you

Let's say A1 has a value of 5 in it. In B1 I have a formula that calculates a new value. If that new value is 5.7 or if it is 6.3 then I have cell A1 round up or down to 6 in this instance. What I also need that I can't figure out is how to have cell A1 increase a max of 1 or decrease a max of 2. So if the new value is 7.8 in B1 the value of 5 in A1 will only increase to 6. If the new value in B1 ends up being 2.2, the value in A1 will only decrease to 3.

i would like to have a set of prices round up or down to the nearest ninth.
for example, 1.15 turns into 1.19. 2.41 turns into 2.39

alright , i did some currency exchange and after i convert the figures, the figure were like $24,567.

how do i apply the formula of rounding to make it $25,000. (ROUND UP)

and how to make values like $24,300 to $24,000. (ROUND DOWN)

how do i apply both the (IF) and (rounding) formulas ?

Round up if > 501 . round down it lower than 500 or equals to.

example of figures , 38,700,600,116.

getting rid of the 116.


I'm having a problem finding a solution to rounding up or down.

Basically, I calculate values and if they fall below 0.5 of a number i wish to round them down, if they go abvoe 0.5 of a number then to round up.

In cell A14 i have the Round Up function but I wish to have a funciton that either rounds up or down depneding on what the value in B14 is, in this instance i wish it to Round Down at it is below 0.5 of the number (4.32)

Hope that makes sense and you can help?!

What is the code to round up a range of numbers at certain decimal places?


I'm trying to come up with vba code that will gray out a set of cells when a certain value is enter in another cell. Any help would be greatly appreciated.


I have got a workbook with 20 or more worksheets. Every quarter I need to paste/append new data on these worksheets (which has already got existing data from previous quarters). All worksheets have different rows of data, for eg One worksheet may have 10rows, the other may have 50. Hence when I am writing my code, I cannot specify the destination cell as for eg A11, or A51.
How do I write up a VBA code that looks for the last row of data on these various worksheets and then pastes the new information on the next row(new row).

Looking forward to someone helping me. Thanks in advance.

I want to write a VBA code for processing daily NSE price data which NSE provides.
I want that when I Enter date in my user form the code automatically complete the url and open the file to complete the process.
Where remains constant
and /2008/MAR/cm11MAR2008bhav.csv changes here cm and bhav.csv is also constant
the complete url is as under

Please help me and provide the VBA code


I need to increase some prices by 4.5% and want them to round up/down to the
nearest $10. For example: $2750 * 4.5% = $2873.75. I know how to knock out
the decimal point, but how can I make the formula round the price up/down to
the nearest $10, in this case, $2870.


Trying to come up with VBA code from within Excel that will mail merge a MS Word document called NewComerTaxLeads.doc with the opened data sheet called 2006 in a workbook called NewComerTaxLeads.xls.

I would really appreciate any & all help you can give me. mikeburg

Hi all,
I just registered to this forum, sorry to start with a question.
I have written two formulas in excel and want to extend to many cells in a certain interval and generalize to other similar worksheets. So, I need to write them as vba code. The formulas:


=IF(C3=W$3,100,IF(C3<W$4,TREND($V$3:$V$4,W$3:W$4,C3),IF(C3=W$4,85,IF(C3<W$5,TREND(V$4:V$5,W$4:W$5,C3 ),IF(C3=W$5,5,IF(C3<W$6,TREND($V$5:$V$6,W$5:W$6,C3),0))))))

I appreciate if any1 can help me to come up with vba codes.

thanks in advance

Hi Trevor,

Thanks for reply.If I insist F2 cut off with 4 decimal points,get the answer
by $12,151.41 any formula or solution ?

Thanks !

F2 is actually 18.218016

The result is: 12151.416672

On my calculator, if I multiply 18.218016 by 667, I get 12151.416 but it
only displays 8 digits. I'm surprised that my calculator appears to have
truncated rather than rounded up but there you go.

Looks like Excel got the calculation right. You need to put more decimal
places into the calculator if you want to compare the output.



"TQ" <> wrote in message
> Hi,question as below
> B2=3.6 C2=2440 D2=1220 E2=1700 G2=$667.00
> F2=18.2180(from formula=B2/1000*C2/1000*D2/1000*E2)
> I get the figure $12,151.42 from G2*F2.According to calculator $667.00 *
> 18.2180 should get $12,151.41. Is that any solution to solve the different
> ?I
> want it get exactly same with calculator and using by formula to get F2
> figure.

Hi All,


I have the following a set of Data on sheet1 and look table in sheet 2. My goal is to look for job ref numbers listed in Column A on sheet1 and once these job numbers are found on sheet 2 in column A, I want the value corresponding in Column B on sheet 2. Basically I am trying to find job numbers in current invoice which might be invoiced to us in previous months. Sheet 1 has jobs invoiced for the current month(max rows:1000) and sheet 2 has got a report of all job numbers invoiced on previous 12 months approx rows:4000).I have tried recording macro(if, isna, vookup functions) for this, it works fine for just one data set, but the limitation is that every time i feed in new data sets, i get run time errors and date format problems, basically i always have to do manually my IF(isna(vlookup...... I am looking for VBA code so I can just assign a button and all results(invoiced months from sheet 2) can be feeded in columnB in sheet1 and are marked in red colour.

Data Set:
On Sheet1 On Sheet 2
Column A Column B Column A Column B
3600012345 June 3600011111 April
3600012346 3600022222 May
3600012347 July 3600012345 June
3600012348 3600033333 June
3600012349 3600012347 July

So i know that in above case 3600012345 & 3600012347 has been invoiced to us before in June and July respectively so I will block the payment.

any help would be greatly appreciated.

thanks & regards

Ok making progress but need a little guidance.
Have vba that copies a certain criteria from Gate Log rounds the total hours worked and then paste the data into the Timesheet
see code below:

    Worksheets("Gate_Log").Range("A2:B300").Copy Worksheets("Timesheet").Range("B2") 
    Worksheets("Gate_Log").Range("D2:D300").Copy Worksheets("Timesheet").Range("D2") 
    Worksheets("Gate_Log").Range("E2:E300").Copy Worksheets("Timesheet").Range("L2") 
    Dim OneCell As Range 
    For Each OneCell In Range(Range("L2"), Range("L" & Rows.Count).End(xlUp)) 
        OneCell.Formula = "=Round(" & OneCell.Value & ",0)" 
    Next OneCell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

would like to have the code automatically deduct 30 minutes from the total hours on the gate log and then round it to the nearest 1/2 hour based on 15 minute scale and then paste that value in column L of Timesheet. Current code works great but is rounding up or down based on .50 scale wrather than .15 ,ie; 15 minutes.

Gate Log = 7.45 - 30 minutes = 7.15 then rounded = 7.00
Gate Log = 7.46 - 30 minutes = 7.16 then rounded = 7.30

I think I posted the code correctly, don't want to offend anyone here, due to my own short comings.

any guidance would be greatly appreciated.

i have had a look for some round up threads but they seem to only exist for spreadsheets.
Is it possible to round the values in vba code?

i need to round up the results from the code below.
eg 2.358 would become 3

sheetquantity = l + sheetlength / 0.76 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
these results are not actually seen anywhere so i carnt do it with a control property


This should be simple but I can't seem to find an answer by searching the forum. I am calculating the number of remaining weeks in a period by simply dividing number of days by seven and defining the resulting variable as an integer. This obviously gives me an answer in the format that I want but I require the "weeks" variable to always round up and currnetly it will round up or down dependant on the result of the division. Easy enough to do as a formula on the worksheet but how can I do this in code.




I am currently working on spreadsheet which is using a mix of Conditional Formatting, Validation and a bit of VBA code.

The spreadsheet is a log of vacancies and applicants, When "vacancy" is selected in A I have introduced some VBA code to lock down cells S:AT on the same row.

The code is working but the issue is it is stopping you using the drop down validation throughout the sheet, if you click the arrow that appears on the cell nothing happens, However you can still access the dropdown by right clicking and choosing the 'Pick from Drop-Down List' Menu Item.

The validation is set up by ranges from another Worksheet.

The Code I am using is:

    Dim myrow 
    Dim StarLock, EnLock 
    StarLock = 19 
    EnLock = 46 
    Application.EnableEvents = False 
    Application.ScreenUpdating = False 
    Application.CutCopyMode = False 
    If Target.Column = 1 Then 
        myrow = Target.Row 
        If Cells(myrow, 1) = "Vacancy" Then 
            Range(Cells(myrow, StarLock), Cells(myrow, EnLock)).Select 
             'With Selection.Interior
             '.ColorIndex = 16
             '.Pattern = xlSolid
             'End With
            Selection.Locked = True 
            Cells(myrow, 1).Select 
        End If 
         '    Else
        If Cells(myrow, 1) = "Applicant" Then 
            Range(Cells(myrow, StarLock), Cells(myrow, EnLock)).Select 
             'With Selection.Interior
             '.ColorIndex = 34
             '.Pattern = xlSolid
             ' End With
            Selection.Locked = False 
            Cells(myrow, 1).Select 
             '        Range(Cells(myrow, Starlock), Cells(myrow, Enlock)).Select
             'With Selection.Interior
             '.ColorIndex = 2
             '.Pattern = xlSolid
             'End With
             '        Selection.Locked = False
             '        Cells(myrow, 1).Select
             '        ActiveSheet.Protect
        End If 
    End If 
    Application.EnableEvents = True 
    Application.ScreenUpdating = True 
    Application.CutCopyMode = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be much appreciated.

I am unable to upload the file to this website due to security restrictions but it can be found here:

Many Thanks,

Hi all,
I have came across some VBA code regarding rounding up of values:

    x = .Evaluate("=ROUNDUP(MAX(C:C),0)") 
    y = .Evaluate("=ROUNDUP(MIN(D:D),0)") 
    x = x * 1.001 
    y = y * 0.997 
    x = WorksheetFunction.Ceiling(x, 1) 
    y = WorksheetFunction.Floor(y, 1) 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I do not understand them at all ~_~. Can anyone explain to me what each line does?

Thanks in advance

How do you Round up to the nearest 0.5 (ie: 0.5, 1.0, 1.5, 2.0, etc) using VBA code?

Round((TextBox1.Value * textBox2.Value),1) doesn't work.

No luck finding an answer? You could always try Google.