Selecting Meals at Random for a Happy Life

In the exciting world of solutions looking for a problem to solve may I offer you this little gem.

As mentioned elsewhere I recently found a place that delivers meals free for $5 each if you order 10 at a time and having sampled them I am determined to make more use of the service. They offer about 15 meals and half a dozen deserts from this menu:

I wanted to select 10 meals at random to fill my freezer so here is a quick and dirty solution. Naturally it can be modified to select any number of items from any list at random.

This is the basic spreadsheet listing the available meals.

…and here is the VBA code that makes it work when you press the command button. The lines which start with a single quote are merely comments to explain what is happening and take no part in the action.:

Option Explicit

Sub SelectMeals()

Dim Lnth As Long
Dim Numb As Long
Dim Nxt As Long
Dim Slct As Long
Dim Meal As String

'Counter for meals selected
Nxt = 1

'Number of meals to Select (could be a cell value)
Slct = 10

'Length of meals listed
Lnth = Sheets(“Sheet1”).Cells(Rows.Count, “A”).End(xlUp).Row

Do

'Generate random number
Numb = Int(1 + Rnd * (Lnth - 1 + 1))

'Collect name of meal
Meal = Sheets(“Sheet1”).Range(“A” & Numb).Value

'Check to see if meal already selected
If IsError(Application.Match(Meal, Sheets(“Sheet2”).Range(“A:A”), 0)) Then

'if not previously selected add meal to list and increment number of meals selected Sheets(“Sheet2”).Range(“A” & Nxt).Value = Meal
Nxt = Nxt + 1

End If

Loop Until Nxt = Slct + 1

'Autofit column
Sheets(“Sheet2”).Range(“A1”).EntireColumn.AutoFit

'Print out list on your printer
Sheets(“Sheet2”).Range(“A1:A” & Lnth).PrintOut

'Clear the list ready for next time
Sheets(“Sheet2”).Range(“A1:A” & Lnth).Clear

End Sub

Well there you are… Get on with it!

1 Like

I don’t know why the bit in the middle appears as a Quote something in it has fallen foul of the forum software

Solved it - it didn’t like the indenting.

Carry on :wink:

I might order some of those, but there is not number, on there, to order for deliveries to the UK!

:honey_pot:

Very true.

Where this all started

That’s a good list for me,nothing too poncified.
Lambs Fry is what we’d call Liver in the UK.Delicious!

Wouldn’t it have been easier to just pick 10 meals Bruce?

Of course it would but where’s the fun in that?

1 Like

I think by the time you have analysed all that data… go simplified…egg and chips self made…

you want more…

1 Like

I think it’s time to get that camper out… you’re in danger of losing your mind if you think that spreadsheet menu dev is fun :star_struck:

To be fair it probably took marginally longer to develop than it did for you to read it. There is nothing in the code that is complex enough to need to look up. What else is one to do in the evening when there is nothing on tele? (and there never is anything worth watching on tele). It makes a change from Sudoku.

As for the camper, not until early next year, everybody and his dog is on the road at the moment, if they aren’t flooded out.

I am braving public transport later in the week to go to Sydney on the train if they aren’t on strike - it will be my first visit to Sydney since 2019.

Anyway got to get on the phone to order my ten random meals before 11am.

Great idea I hate cooking .

1 Like