I am presently trying to write a script that would pull a random meal from a list of meals and input them in a calendar. It generates 4 weeks (Sunday through Saturday), with two different meals per week. I would like to have it check each time it pulls a random meal from the list against the meals that it has already pulled to prevent the same meal from coming up twice in one month. Following is what I have written:
Dim ranDnr As Integer
Dim ranDnr2 As Integer
Dim usdMls(3) As Integer
Dim usdMls2(3) As Integer
Sub GnrtMlsMnthClndr()
Randomize
For i = 0 To 3
For j = 0 To 3
For x = 2 To 17 Step 5
For y = 2 To 17 Step 5
ranDnr = Int(60 * Rnd + 1)
While IsInArray(ByVal ranDnr, ByVal usdMls) = True
ranDnr = Int(60 * Rnd + 1)
Wend
usdMls(i) = ranDnr
Worksheets("Sheet1").Cells(x, 1) = Worksheets("Sheet2").Cells(ranDnr, 1)
ranDnr2 = Int(60 * Rnd + 1)
While IsInArray2(ByVal ranDnr2, ByVal usdMls2) = True
ranDnr2 = Int(60 * Rnd + 1)
Wend
usdMls2(j) = ranDnr2
Worksheets("Sheet1").Cells(x, 9) = Worksheets("Sheet2").Cells(ranDnr2, 1)
Next y
Next x
Next j
Next i
End Sub
Public Function IsInArray(ByVal ranDnr, ByVal usdMls) As Boolean
'Dim i
For i = 0 To 3
If usdMls(i) = ranDnr Then
IsInArray = True
Exit Function
End If
Next i
IsInArray = False
End Function
Public Function IsInArray2(ByVal ranDnr2, ByVal usdMls2) As Boolean
'Dim i
For i = 0 To 3
If usdMls2(i) = ranDnr2 Then
IsInArray2 = True
Exit Function
End If
Next i
IsInArray2 = False
End Function
I am really inexperienced in VBA and have absolutely no idea why this doesn't work, as it seems logical to me and doesn't generate any errors when I run it. Any assistance that could be provided would be appreciated.
