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?
Sub cmdFind()
'declare the variables

Dim ProID1 As Integer
Dim unit1 As String
Dim Qty1 As Long

'Declare the data range
Set myrange = Sheets("Database").Range("tblProducts")

ProID1 = Me.cboIng1.Column(0)
unit1 = Me.cboUnit1.Value
Qty1 = Me.txtQty1.Value

If unit1 = "Kilograms" Then
Me.Cost1 = Application.WorksheetFunction.VLookup(ProID1, myrange, 16, 0) * Qty1
Else
If unit1 = "Grams" Then
Me.Cost1 = Application.WorksheetFunction.VLookup(ProID1, myrange, 17, 0) * Qty1
Else
If unit1 = "Pounds" Then
Me.Cost1 = Application.WorksheetFunction.VLookup(ProID1, myrange, 18, 0) * Qty1
Else
If unit1 = "Ounces Dry" Then
Me.Cost1 = Application.WorksheetFunction.VLookup(ProID1, myrange, 19, 0) * Qty1
Else
If unit1 = "Liter" Then
Me.Cost1 = Application.WorksheetFunction.VLookup(ProID1, myrange, 20, 0) * Qty1
Else
If unit1 = "Mils" Then
Me.Cost1 = Application.WorksheetFunction.VLookup(ProID1, myrange, 21, 0) * Qty1
Else
If unit1 = "Each" Then
Me.Cost1 = Application.WorksheetFunction.VLookup(ProID1, myrange, 22, 0) * Qty1
Else
If unit1 = "Ounces Liquid" Then
Me.Cost1 = Application.WorksheetFunction.VLookup(ProID1, myrange, 23, 0) * Qty1
End If
End If
End If
End If
End If
End If
End If
End If
Me.txtCost.Value = Me.Cost1 + Me.Cost2 + Me.Cost6 + Me.Cost4 + Me.Cost5 + Me.Cost6 + Me.Cost7 + Me.Cost8 + Me.Cost9 + Me.Cost10
End Sub