Help with Loops

**omagoodness** · 04-18-2016, 01:40 PM

Hi again: I have a user form that allows users to create a recipe. The form has several textboxes and combo boxes to enter 10 ingredients. The control names are Qty1, cboUnit1, cboIng1; Qty2, cboUnit2, cboIng2; etc. all the way to 10. The user can enter the amount in Qty, select the unit (grams, kilos, ounces, etc.) from cboUnit and select the ingredient from cboIng.
I need to look up the cost of each ingredient in tblProducts based on the unit selected.
I have been able to get the desired result by using code as below for each control. What I am asking, is there a way to do a loop of some kind without having to have so much code since I have to repeat this code for each of the 10 ingredients?

Please Login or Register  to view this content.

**nilem** · 04-18-2016, 01:55 PM

Hi omagoodness,
maybe something like this (not tested)

Please Login or Register  to view this content.

**Norie** · 04-18-2016, 02:02 PM

Perhaps.

Please Login or Register  to view this content.

**omagoodness** · 04-18-2016, 05:23 PM

I tried both this suggestion as well as the one below suggested by nilem and both of them result in

error 381 Could not get the column property. Invalid property array index.

Any other suggestions? I will use the extensive simple version if I must but I hate to.

**Norie** · 04-18-2016, 05:48 PM

When do you get the error?

Is it when one of the comboboxes hasn't been used?

Any chance you could upload a sample workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

**omagoodness** · 04-18-2016, 06:27 PM

The comboboxes will rarely all be used but the option needs to be available. It's frmRecipes that I am referring to. When a product is purchased, it is added to the database in tblProducts, or a price change is made as required. The formulas in the green cells calculate the cost of each unit based on the total price paid.
When the user enters recipes, the information is stored in tblRecipes along with the food cost for that recipe. Therefore, I need to be able to calculate the cost of each ingredient in the recipe and then add them together for a recipe cost. Eventually this data will be used to cost out the menu plates as well but that step is down the road a bit.
Although I am quite familiar with entering formulas in spreadsheets, doing vba code in Excel is very new to me. This is quite an ambitious project for a newbie but if I get it accomplished - which I am determined to do with what will amount to a lot of help from this forum

- then I will have a much better understanding of the whole thing.