Sub LoopFinal()
'This is the final loop that will generate a dish that meets all the criterias.
Dim row As Integer 'Variable that will be used for the FOR loop, representing the first row
Dim backEnd As Worksheet 'This variable represents the backEnd sheet, where the items meeting the client's specification will be stored.
Dim dietSheet As Worksheet 'dietSheet is a variable that represents the dietSheet sheet.
Dim cat As String 'This represents the catagory of the dish that is being iterated through the list.
Dim cal As Integer 'This represents the meal type of the dish that is being iterated through the list.
Dim typeMeal As String 'This represents the meal type of the dish that is being iterated through the list.
Dim catBool As Boolean 'Boolean that will return false if the client's calorie specification input is empty.
Dim mealBool As Boolean 'Boolean that will return false if the client's meal type specification input is empty.
Dim fillBool As Boolean 'Boolean that will return false if the client's fill level specification input is empty.
Dim catValue As String 'String variable that will represent the category specification that the client inputs.
Dim calValue As Integer 'Integer variable that will represent the calorie value specification that the client inputs.
Dim typeMealInput As String 'String variable that will represent the meal type specification that the client inputs.
Dim fillLevelInput As String 'String variable that will represent the fill level specification that the client inputs.
Dim fillLevel As String 'This variable represents the fill level of the dish that is being iterated through the list.
Dim protLevelInput As Integer 'Integer variable that will represent the protein amount specification inputted by the client.
Dim protLevel As Integer 'This variable represents the protein amount of the dish that is being iterated through the list.
Dim lastRow As Long 'Variable that will represent the last row where data is stored
Dim counter As Integer 'Variable that will represent the row the dish/food and it's specifications will be pasted in the backEnd
'Assigning variables to correct sheets
Set dietSheet = Sheet3
Set backEnd = Sheet4
lastRow = backEnd.Cells(Rows.Count, 1).End(xlUp).row 'Assigning last row to the variable
counter = 0 'Intializing the counter variable
If IsEmpty(dietSheet.Range("A6").Value) = False Then 'If the user input for the category specification is not empty.
catBool = True
catValue = dietSheet.Range("A6").Value 'CATINPUT will be assigned the user input
Else
catBool = False
End If
If IsEmpty(dietSheet.Range("B6").Value) = False Then 'If the user input for the calorie specification is not empty.
calValue = dietSheet.Range("B6").Value 'CALINPUT will be assigned the user input
Else
calValue = 10000 'This is because if the client has no specification for a calorie value, it can therefore be large, and no calorie count of an eatable dish can exceed 10000.
End If
If IsEmpty(dietSheet.Range("C6").Value) = False Then 'If the user input for the meal type specification is not empty.
mealBool = True
typeMealInput = dietSheet.Range("C6").Value 'MEALTYPEINPUT will be assigned the user input
Else
mealBool = False
End If
If IsEmpty(dietSheet.Range("D6").Value) = False Then 'If the user input for the fill level specification is not empty.
fillBool = True
fillLevelInput = dietSheet.Range("D6").Value 'FILLLEVELINPUT will be assigned the user input
Else
fillBool = False
End If
If IsEmpty(dietSheet.Range("E6").Value) = False Then 'If the user input for the protein specification is not empty.
protLevelInput = dietSheet.Range("E6").Value 'PROTEININPUT will be assigned the user input
Else
protLevelInput = 10000 'This is because if the client has no specification for a protein value, it can therefore be large, and no protein count of an eatable dish can exceed 10000.
End If
For row = 1 To lastRow 'loop through entire item list table
'Assigning the specification of the food being iterated through the table to variables
cat = backEnd.Cells(row, 2).Value
cal = backEnd.Cells(row, 3).Value
typeMeal = backEnd.Cells(row, 4).Value
fillLevel = backEnd.Cells(row, 5).Value
protLevel = backEnd.Cells(row, 6).Value
'These nested If's will determine the which methods to call based on the client inputted specifications and will call the PasteMeal method in order to paste the correct meals in the backEnd
If catBool Then 'If CATINPUT = NOT Empty
If mealBool Then
If fillBool Then
If cat = catValue And cal <= calValue And typeMeal = typeMealInput And fillLevelInput = fillLevel And protLevel <= protLevelInput Then 'Runs if user inputs all specifications
Call pasteMeal(row, counter, backEnd) 'Call pasteMeal AND pass through ROW variable AND counter in order to paste the meal and specifications in backEnd
End If
Else
If cat = catValue And cal <= calValue And typeMeal = typeMealInput And protLevel <= protLevelInput Then
Call pasteMeal(row, counter, backEnd)
End If
End If
Else
If fillBool Then
If cat = catValue And cal <= calValue And fillLevelInput = fillLevel And protLevel <= protLevelInput Then
Call pasteMeal(row, counter, backEnd)
End If
Else
If cat = catValue And cal <= calValue And protLevel <= protLevelInput Then
Call pasteMeal(row, counter, backEnd)
End If
End If
End If
Else
If mealBool Then
If fillBool Then
If cal <= calValue And typeMeal = typeMealInput And fillLevelInput = fillLevel And protLevel <= protLevelInput Then
Call pasteMeal(row, counter, backEnd)
End If
Else
If cal <= calValue And typeMeal = typeMealInput And protLevel <= protLevelInput Then
Call pasteMeal(row, counter, backEnd)
End If
End If
Else
If fillBool Then
If cal <= calValue And fillLevelInput = fillLevel And protLevel <= protLevelInput Then
Call pasteMeal(row, counter, backEnd)
End If
Else
If cal <= calValue And protLevel <= protLevelInput Then
Call pasteMeal(row, counter, backEnd)
End If
End If
End If
End If
Next row
'Ensures that the data is inserted in the correct location without any whitespace
If IsEmpty(backEnd.Range("H1")) = True Then
backEnd.Rows(1).Delete
End If
End Sub
Sub pasteMeal(row As Integer, counter As Integer, backEnd As Worksheet)
'This method pastes the meal in the BACKEND table
counter = counter + 1 'Update the counter when the method is called
'Paste the meal that matches all the specifications inputted by the client
backEnd.Select
Range(Cells(row, 1), Cells(row, 7)).Select
Selection.Copy
Cells(counter, 8).Select
Selection.PasteSpecial Paste:=xlPasteValues
End Sub
Sub DifferentMeal()
'This method will be used to provide alternative meals that meet the specifications if the client does not like the initial outputted meal.
Dim dietSheet As Worksheet 'dietSheet is a variable that represents the dietSheet sheet.
Dim backEnd As Worksheet 'This variable represents the backEnd sheet, where the items meeting the client's specification will be stored.
Dim lastRow As Integer 'Variable that will represent the last row of the table, in this method, it will illustrate the last row of the table in the BACKEND sheet.
Dim randomRow As Integer 'Variable that will store a random integer between 1 to lastRow, this will be used to randomly select a row in the table of foods that meet the client's specifications.
'Assigning the sheets to the correct variables
Set backEnd = Sheet4
Set dietSheet = Sheet3
'Assigning variables
lastRow = backEnd.Cells(Rows.Count, 8).End(xlUp).row
randomRow = WorksheetFunction.RandBetween(1, lastRow)
If IsEmpty(backEnd.Range("H1")) = True Then 'If table empty, it means no meal is found meeting the client's specifications
MsgBox "No replacement meal found", vbInformation 'OUTPUT "No replacement meal found" through message box
Else
'Copy food and it's specifications in randomRow and paste it in the dietSheet, where the food and it's specifications will be outputted
backEnd.Select
Cells(randomRow, 8).Select
Selection.Copy
dietSheet.Select
Cells(11, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
backEnd.Select
Range(Cells(randomRow, 9), (Cells(randomRow, 14))).Select
Selection.Copy
dietSheet.Select
Cells(15, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
backEnd.Select
Range((Cells(randomRow, 8)), Cells(randomRow, 14)).Select
Selection.Delete
dietSheet.Select
End If
End Sub
Sub MoreFoodLoopTable()
'This method will be used to find food that the client can eat (still meeting the calorie count) along with the outputted meal and paste it into the BACKEND2 sheet
Dim backEnd2 As Worksheet 'This variable represents the backEnd2 sheet, where a table containing food that the client can eat with the meal (still meeting the calorie count) will be stored.
Dim dietSheet As Worksheet 'dietSheet is a variable that represents the dietSheet sheet.
Dim listSheet As Worksheet 'This variable represents the listSheet, where the full list of all food items and their details are stored.
Dim wantedCal As Integer 'Variable representing the calorie specification of the client
Dim returnedCal As Integer 'Variable representing the calorie of the meal that has been outputted
Dim fullCal As Integer 'Variable representing the WANTEDCAL - RETURNEDCAL, highlighting the amount of calories left that the client can consume
Dim row As Integer 'Variable that will be used for the FOR loop, representing the first row of the table.
Dim counter As Integer 'Variable that will represent the row the dish/food and it's specifications will be pasted in the backEnd2 sheet
Dim foodName As String 'Name of the food/dish being iterated through
Dim cal As Integer 'Variable representing the calorie of the food being iterated through the table in the listSheet
Dim lastRow As Long 'Variable that will represent the last row of the table.
'Assigning sheets to the variables
Set backEnd2 = Sheet7
Set dietSheet = Sheet3
Set listSheet = Sheet2
'Assigning variables to values
wantedCal = dietSheet.Range("B6").Value
returnedCal = dietSheet.Range("B15").Value
fullCal = wantedCal - returnedCal 'Amount of calories left that the client can consume.
backEnd2.Range("A:B").Clear 'Clear the sheet in order to accept new data entry
lastRow = listSheet.Cells(Rows.Count, 1).End(xlUp).row 'Assigning lastRow
counter = 0 'Initialize the variable
For row = 2 To lastRow 'Loop through the table of foods in the listSheet
'Assigning variables
cal = listSheet.Cells(row, 3).Value
foodName = listSheet.Cells(row, 1).Value
If cal <= fullCal Then 'If the calorie of the food being iterated through the table is less than the amount of calories the client can consume after the meal already outputted.
counter = counter + 1 'Update counter
'Copy dish and it's specifications meeting the calorie specification stated in the if statement
listSheet.Select
listSheet.Cells(row, 1).Select
Selection.Copy
'Paste it in the next available row in the BACKEND2 sheet
backEnd2.Select
backEnd2.Cells(counter, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
listSheet.Select
listSheet.Cells(row, 3).Select
Selection.Copy
backEnd2.Select
backEnd2.Cells(counter, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues
End If
Next row
End Sub
Sub MoreNewFood()
'This method will be used to randomly output additional food/dish the client can eat, generated from the MoreFoodLoopTable
Dim dietSheet As Worksheet 'dietSheet is a variable that represents the dietSheet sheet.
Dim backEnd2 As Worksheet 'This variable represents the backEnd2 sheet, where a table containing food that the client can eat with the meal (still meeting the calorie count) will be stored.
Dim lastRow As Long 'Variable that will represent the last row of the table.
Dim randomRow As Integer 'Variable that will store a random integer between 1 to lastRow, this will be used to randomly select a row of food in the table in BACKEND2
'Assigning variables to the correct sheets
Set backEnd2 = Sheet7
Set dietSheet = Sheet3
'Assigning variables correct values
lastRow = backEnd2.Cells(Rows.Count, 1).End(xlUp).row
randomRow = WorksheetFunction.RandBetween(1, lastRow)
'Copy random meal from backEnd2 and output to the user in correct locarion
backEnd2.Select
Cells(randomRow, 1).Select
Selection.Copy
dietSheet.Select
Cells(18, 11).Select
Selection.PasteSpecial Paste:=xlPasteValues
backEnd2.Select
Cells(randomRow, 2).Select
Selection.Copy
dietSheet.Select
Cells(18, 12).Select
Selection.PasteSpecial Paste:=xlPasteValues
'Delete from table, because it has been outputted
backEnd2.Select
Range(Cells(randomRow, 1), Cells(randomRow, 2)).Select
Selection.Delete
dietSheet.Select
If IsEmpty(Range("L18")) = True Then 'If Table in BACKEND2 is empty Then
MsgBox "No meal found", vbInformation 'Output message box
End If
End Sub
Sub clearAllButton()
'Clear all in the trackVisual sheets to accept new graphs and tables.
Range("A:W").Clear
Worksheets("trackVisual").ChartObjects.Delete
End Sub
We use cookies to provide and improve our services. By using our site, you consent to our Cookies Policy. Accept Learn more
