Looping Through Userform Controls

**omagoodness** · 05-23-2016, 08:55 AM

Hello again:
I am currently using the following macro to calculate the cost of a recipe entered using a userform (frmRecipeBox) and then storing the information in tblRecipes on Sheets("Recipe Box"). My question is this, is it possible to reduce the amount of code by using a loop? I have played around with somethings and did a lot of searching but can't find amything that works for me. Again, I am very new at all this and learning as I go. I actually need to calculate the cost of 15 ingredients making the code almost 3 times the amount however that is too much text to post here. I am afraid that so much code will invite trouble at some point.

Sub CalculateRecipeCost()
Set MyRange = Sheets("Inventory").Range("tblProducts") 'data range

'Calculate the cost of ingredient 1
Dim myAmt1 As String, myIng1 As String, myUnit1 As String

myAmt1 = frmRecipeBox.rec5.Value 'quantity
myIng1 = frmRecipeBox.rec4.Value 'ingredient
myUnit1 = frmRecipeBox.rec6.Value 'unit



If myAmt1 = vbNullString Then
frmRecipeBox.Cost1 = 0
Else
    If myUnit1 = "Kilograms" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 17, 0) * myAmt1
Else
    If myUnit1 = "Grams" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 18, 0) * myAmt1
 Else
    If myUnit1 = "Pounds" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 19, 0) * myAmt1
Else
    If myUnit1 = "Ounces Dry" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 20, 0) * myAmt1
Else
    If myUnit1 = "Liter" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 21, 0) * myAmt1
Else
    If myUnit1 = "Mils" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 22, 0) * myAmt1
 Else
    If myUnit1 = "Each" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 23, 0) * myAmt1
 Else
    If myUnit1 = "Ounces Liquid" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 24, 0) * myAmt1
End If
End If
End If
End If
End If
End If
End If
End If
End If

'Calculate the cost of ingredient 2
Dim myAmt2 As String, myIng2 As String, myUnit2 As String

myAmt2 = frmRecipeBox.rec9.Value 'quantity
myIng2 = frmRecipeBox.rec8.Value 'ingredient
myUnit2 = frmRecipeBox.rec10.Value 'unit


If myAmt2 = vbNullString Then
frmRecipeBox.Cost2 = 0
Else
    If myUnit2 = "Kilograms" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 17, 0) * myAmt2
Else
    If myUnit2 = "Grams" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 18, 0) * myAmt2
 Else
    If myUnit2 = "Pounds" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 19, 0) * myAmt2
Else
    If myUnit2 = "Ounces Dry" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 20, 0) * myAmt2
Else
    If myUnit2 = "Liter" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 21, 0) * myAmt2
Else
    If myUnit2 = "Mils" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 22, 0) * myAmt2
 Else
    If myUnit2 = "Each" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 23, 0) * myAmt2
 Else
    If myUnit2 = "Ounces Liquid" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 24, 0) * myAmt2
End If
End If
End If
End If
End If
End If
End If
End If
End If

'Calculate the cost of ingredient 3
Dim myAmt3 As String, myIng3 As String, myUnit3 As String

myAmt3 = frmRecipeBox.rec13.Value 'quantity
myIng3 = frmRecipeBox.rec12.Value 'ingredient
myUnit3 = frmRecipeBox.rec14.Value 'unit


If myAmt3 = vbNullString Then
frmRecipeBox.Cost3 = 0
Else
    If myUnit3 = "Kilograms" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 17, 0) * myAmt3
Else
    If myUnit3 = "Grams" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 18, 0) * myAmt3
 Else
    If myUnit3 = "Pounds" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 19, 0) * myAmt3
Else
    If myUnit3 = "Ounces Dry" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 20, 0) * myAmt3
Else
    If myUnit3 = "Liter" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 21, 0) * myAmt3
Else
    If myUnit3 = "Mils" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 22, 0) * myAmt3
 Else
    If myUnit3 = "Each" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 23, 0) * myAmt3
 Else
    If myUnit3 = "Ounces Liquid" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 24, 0) * myAmt3
End If
End If
End If
End If
End If
End If
End If
End If
End If

'Calculate the cost of ingredient 4
Dim myAmt4 As String, myIng4 As String, myUnit4 As String

myAmt4 = frmRecipeBox.rec17.Value 'quantity
myIng4 = frmRecipeBox.rec16.Value 'ingredient
myUnit4 = frmRecipeBox.rec18.Value 'unit


If myAmt4 = vbNullString Then
frmRecipeBox.Cost4 = 0
Else
    If myUnit4 = "Kilograms" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 17, 0) * myAmt4
Else
    If myUnit4 = "Grams" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 18, 0) * myAmt4
 Else
    If myUnit4 = "Pounds" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 19, 0) * myAmt4
Else
    If myUnit4 = "Ounces Dry" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 20, 0) * myAmt4
Else
    If myUnit4 = "Liters" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 21, 0) * myAmt4
Else
    If myUnit4 = "Mils" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 22, 0) * myAmt4
 Else
    If myUnit4 = "Each" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 23, 0) * myAmt4
 Else
    If myUnit4 = "Ounces Liquid" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 24, 0) * myAmt4
End If
End If
End If
End If
End If
End If
End If
End If
End If


'Calculate the cost of ingredient 5
Dim myAmt5 As String, myIng5 As String, myUnit5 As String

myAmt5 = frmRecipeBox.rec21.Value 'quantity
myIng5 = frmRecipeBox.rec20.Value 'ingredient
myUnit5 = frmRecipeBox.rec22.Value 'unit


If myAmt5 = vbNullString Then
frmRecipeBox.Cost5 = 0
Else
    If myUnit5 = "Kilograms" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 17, 0) * myAmt5
Else
    If myUnit5 = "Grams" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 18, 0) * myAmt5
 Else
    If myUnit5 = "Pounds" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 19, 0) * myAmt5
Else
    If myUnit5 = "Ounces Dry" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 20, 0) * myAmt5
Else
    If myUnit5 = "Liter" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 21, 0) * myAmt5
Else
    If myUnit5 = "Mils" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 22, 0) * myAmt5
 Else
    If myUnit5 = "Each" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 23, 0) * myAmt5
 Else
    If myUnit5 = "Ounces Liquid" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 24, 0) * myAmt5
End If
End If
End If
End If
End If
End If
End If
End If
End If

'Add the 5 ingredient amounts together for total recipe cost

frmRecipeBox.rec65.Value = Format(Val(Replace(frmRecipeBox.Cost1.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost2.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost3.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost4.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost5.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost6.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost7.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost8.Value, ",", "")) + _
   Val(Replace(frmRecipeBox.Cost9.Value, ",", "")) + _
   Val(Replace(frmRecipeBox.Cost10.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost11.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost12.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost13.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost14.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost15.Value, ",", "")))
End Sub

Looping Through Userform Controls

LinkBack

Thread Tools

Rate This Thread

Display

Threaded View

Looping Through Userform Controls

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Looping through controls by frame on userform (frame within a frame)

Looping through label controls

looping controls in to an array

looping through controls

Looping through controls

Looping through controls on form

[SOLVED] Problem with looping through userform controls

Bookmarks

Bookmarks

Posting Permissions