Nested IF loops More than 7 Need Help

Hello All,

So ive got a pull down menu in a cell. Depending on what item is selected from the pull down menu a constant intager will be multiplied by another user input integer in a seperate cell. the result will be reported in another cell. For example.

If the user selects "3 Inch Binder" and then reports they need 3 of them. The system must calculate that each binder is 1 dollar. thus it will report 3 dollars.

Now if they select "5 inch Binder" and again report they need 3, the system must now use the constant 1.50 per binder. and thus report the total as 4.50.

I can do this easily with nested IF loops, but i have 10 items on my list, meaning i need to use VBA.

This is my code so far....what am i doing wrong?

Please Login or Register  to view this content.

This isnt working at all however.

Any Ideas? ( I have proper indention in the code itself.)

NOTE: Quan will be the user input for how many of the item they need. The constants being multiplied in the code correspond to the price per unit for that item. Hence why Quan is multiplied by the price per unit to give us the final amount to be reported in the cell where the formula is called.

Please Login or Register  to view this content.

For the future, though:
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Thank you for the reply!

And for the future i will always place my code in format.

I think i may have done a bad job at explaining what im trying to do.

lets say:

cell A1 has a pull down menu, that menu consists of 10 items ( item1, item2, item3,item4, item5, etc.)

Each item has a price value associated with it.

in cell A2 the user will input a quanitity of the item they want.

So again, They select the item they need in cell A1 from a pull down list, and then in A2 report how many of the item they would like to buy.

cell A3, will then calculate how much they owe, but multiplying the price of the selected item by the quanitity they request.


I can easily do this by just nesting loops by saying:

IF(A1= item1, A2*2, IF(A1=item2, A2*3.50, IF(A1=item3, A2*4.50, IF(....... and so on till i reach item10.

NOTE: This above function implys Item1 has a value of 2.00. item2 has a value of 3.50, item3 has a value of 4.50. etc.

However since excel only allows me to nest 7 functions i can not do this up to item 10...

Thank you for all