If the number is in A2 just use this formula
To round to the NEAREST 500: (eg. 524 rounds to 500)
=ROUND(A2/500,0)*500
To round to the NEXT HIGHEST 500: (eg. 524 rounds to 1000)
=CEILING(A2,500)
1. INDEX formula:
=INDEX(RANGE,ROWnumber,COLUMNnumber)
So in the example above, to be able see the price for 20 apples, you use:
=INDEX(B2:D4,1,2) ==> Result is $2
2. MATCH formula:
=MATCH(LOOKUPValue,LOOKUPArray,MATCHType)
So in the example above, to know the column for Apple, you use:
=MATCH(”Apple”,B1:D1,0) ==> Result is 3
3. Using names:
Using names makes the formula easy to read, and very flexible. If your data sheet gets more columns or rows, it doesn’t matter if you set up the formula like this:
Define names: (insert, name, define)
Data –> =Sheet1!A1:Sheet1!AD20000
DataRow –> =Sheet1!1:1
DataCol –> =Sheet1!A:A
4. Combine the formulas
=INDEX(Data,MATCH(”Apple”,DataCol,0),MATCH(20,DataRow,0))
Ofcourse you can use input fields for your criteria on another sheet, because of the names you defined on the data sheet, you can put these on any other sheet you want!
Let’s say you put your fruit type in A1, and the number of fruit in A2, and you want A3 to show the price. You put this formula in A3:
=INDEX(Data,MATCH(A2,DataCol,0),MATCH(A1,DataRow,0))
There are many times in Excel VBA that we are required to gather information from a user. Probably the most frequent method of doing is via a message box, that is;
As you can though, the message box approach only allows for pre-determined responses. To actually allow the user to enter some text, number or even a formula we can use the InputBox Function. The syntax for the InputBox Function is;
InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
It is rare that you will need to use [, xpos] [, ypos] or [, helpfile, context]. See Excel help for details on these. It should also be noted that, the InputBox Function returns a String only when used in this way. (more on another way soon).
Ok, lets assume we need to gather the name of the user and do some stuff depending on that name. The macro below will achieve this.
Note the use of the Select Case Statement to determine the name the user supplies.
Application.InputBox
When we precede the InputBox Function with "Application" we get an InputBox Method that will allow us to specify the type of info that we can collect. Its Syntax is;
InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)
As you can see, the Prompt, Title and Default are the same as in the InputBox Function. However, it is the last argument "Type" that allows us to specify the type of data we are going to collect. These are as shown below;
Type:=0 A formula
Type:=1 A number
Type:=2 Text (a string)
Type:=4 A logical value (True or False)
Type:=8 A cell reference, as a Range object
Type:=16 An error value, such as #N/A
Type:=64 An array of values
We have already covered a String being returned so lets look, what I believe, to be the most useful. That is, Type 8 & 1. The code below shows how we can allow the user to specify a Range Object.
Note the use of both, On Error Resume Next and Application.DisplayAlerts = False. These stop Excel from trying to handle any bad input from the user, or if they Cancel. Take the lines out, run the code and click Cancel, or specify a non valid range and Excel will bug out in the case of Cancel.
Let's now look at how we can collect a numeric value from a user.
Again, we take over the possibility of the user electing to Cancel, or entering a non-numeric value. If they enter anything that is not numeric and click OK, they are taken back to the InputBox Method with their entry highlighted.
Unlike the InputBox Function, we can combine different Types for the InputBox Method and take action based on their data type. See example.
