ごはんのメニューをランダムに決めるマクロを紹介

Sub ChooseMenu()

  Dim shtMain As Worksheet: Set shtMain = ThisWorkbook.Sheets("sheet1")
  Dim shtMenuData As Worksheet: Set shtMenuData = ThisWorkbook.Sheets("MenuList")

  'メニュー数取得
  Dim lngMenuNum As Long
  lngMenuNum = shtMenuData.Cells(Rows.Count, 1).End(xlUp).Row

  '乱数生成（かぶりNG）
  Dim strMenu(0 To 6, 0) As String '一括貼付けのため2次元配列
  Dim blUsed() As Boolean: ReDim blUsed(lngMenuNum)
  Dim tmpNum As Long
  Dim intI As Integer

  For intI = 0 To 6
    Do
      tmpNum = Int(Rnd * lngMenuNum) + 1
      If blUsed(tmpNum) = False Then
        blUsed(tmpNum) = True 'メニューのかぶり防止
        strMenu(intI, 0) = shtMenuData.Cells(tmpNum, 1).Value
        Exit Do
      End If
    Loop
  Next

  'メニューの表示
  shtMain.Cells(1, 1).Resize(7, 1).Value = strMenu

End Sub