Hello,

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!

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!

- Round up / down date/time format?
- Specific Round up/down
- Rounding Up & Down Problem
- Rounding Problem in VBA
- Formula help - rounding up and round down?
- Round Up/Down But Not More Then
- How to make a number round up/down to a set number
- Rounding(UP/DOWN) formulas
- Rounding up/down
- Rounding up using VBA?
- Want to shade cells Using VBA Code
- VBA CODE on Pasting on Last row of data for various worksheets
- VBA code request for Completing the url for processing the daily NSE stock prices
- I need a %age increase formula that rounds up to the nearest $10
- VBA code merge Word doc with Excel data
- Excel formula to VBA code conversion
- Trevor - Round Up/Down ?
- VBA Code Request for tracing duplicates on invoice
- Vba subtract 30 minutes and round up and down
- Round up VBA code
- Code to round up number
- VBA code for Locking Cell disabling Drop Downs Validation Lists
- Rounding Functions Used In VBA Code
- Round up to nearest 0.5 in VBA

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?

For example:

0.7834 for 0.80

0.6221 for 0.60

0.65 for 0.70

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.

Regards

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.

formulae:

round(tbl.field)+round(tbl.field)

Thanks in advance

Regards

John

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

for example, 1.15 turns into 1.19. 2.41 turns into 2.39

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?!

Thanks,

Tom

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 that when I Enter date in my user form the code automatically complete the url and open the file to complete the process.

Where http://nseindia.com/content/historical/EQUITIES/ remains constant

and /2008/MAR/cm11MAR2008bhav.csv changes here cm and bhav.csv is also constant

the complete url is as under

http://nseindia.com/content/historic...AR2008bhav.csv

Please help me and provide the VBA code

Regards

Samvet

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.

Thanks

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

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:

=INDEX('sheet1'!$A2:$BI$7000,MATCH(MAX('sheet1'!I2:I7),'sheet1'!I2:I7,0),COLUMN('sheet1'!A1))

=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

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.

Regards

Trevor

"TQ" <TQ@discussions.microsoft.com> wrote in message

news:44F18E22-78C7-4946-8D87-4E1418804656@microsoft.com...

> 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.

Goodevening,

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

3600012350

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

Manav

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:

VB:Test() 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 SubIf 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.

Example:

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?

cheers

toe

i need to round up the results from the code below.

eg 2.358 would become 3

VB:these results are not actually seen anywhere so i carnt do it with a control propertysheetquantity = l + sheetlength / 0.76If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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.

Cheers

Simpo

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:

VB:Any help would be much appreciated.Range) 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 ActiveSheet.Unprotect 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 ActiveSheet.Protect 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 ActiveSheet.Protect Else ' 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 ActiveSheet.Protect End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

https://rcpt.yousendit.com/143737095...889d657f64b494

Many Thanks,

Leox5000

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

VB:I do not understand them at all ~_~. Can anyone explain to me what each line does?ws 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 WithIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks in advance

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