VBA抽選ツール「ロッカーの位置を決める君」の開発②

　名簿シートから抽選シートに職員40名分の苗字の一覧をコピーしてくることができました。いよいよこの40名の中から1名をランダムに選ぶためのコードを書いていきます。

ランダムに数字を選ぶことができる関数

　Excelにはある数からある数までの間の数字をランダムに選ぶという関数「RANDBETWEEN 関数」というものがあります。例えば1から10までの数字を一つランダムに選んでA1セルに表示させたいときは、A1セルに「=RANDBETWEEN(1,10)」と入力すればいいということになります。

RANDBETWEEN 関数

下の動画を見ていただくと、ワークシートが再計算されるたびに1から10までの数字からランダムに1つ選んだ数字が表示される様子が分かると思います。この動画ではF9キーを押してワークシートの再計算をしています。

今回は40名の中から1人選ぶので、「＝RANDBETWEEN（1,40）」と書けばよいのですが、この人数は名簿が変更になれば変わってしまいます。40の部分は変数を使って、どのような数でも入るようにしておかなければなりません。
　今日はPADではなくてVBAなので、C12セルから下にいって最後のセルの行番号を取得していきます。Cells(12, 3).End(xlDown).Rowで40番目の中野と入ったセルの行番号51が取得されますね。そうすると、その数からタイトルが入っている行の番号11を引くと、職場の職員の人数になります。つまり、
RandBetween(1, Cells(12, 3).End(xlDown).Row-11)が40になるはずです。

苗字が入っている最後のセルの行番号を取得する

ただ、この関数で取得できるのは１から40までの数字です。C9セルに表示させたいのは苗字でした。なので、取得できた数字をB列の中から探し出して、見つかったらその一つ右のセルに入っている文字をC9セルに表示する、という仕組みにしていきます。

B列から数字を探す

例えば、上の関数で37という数字が得られたとします。そうしたら、B12～B51セルの中から37を探します。もちろんこのセルの範囲も可変です。

37が見つかったらその右隣のセルの値「藤井」を取得して、C9セルに表示していきます。
この一連の流れを書いたのが以下のコードです。

Sub 当選者決め()
Dim ws As Worksheet
Dim lastRow As Long, rd As Long, ninzu As Long
Dim mydata As String

Set ws = ThisWorkbook.Worksheets("抽選")
    '名前一覧に掲載されている名前の個数（＝人数）を数える
    lastRow = ws.Cells(12, 3).End(xlDown).Row
    ninzu = lastRow - 11

    rd = WorksheetFunction.RandBetween(1, ninzu)
    
Dim searchRange As Range, cell As Range
Dim name As String
Set searchRange = ws.Range(Cells(12, 2), Cells(lastRow, 2))
    For Each cell In searchRange
        If cell.Value = rd Then
            name = cell.Offset(0, 1).Value
        End If
    Next cell

    ws.Range("C9").Value = name

End Sub

これで、40人の中から1人を選び出すことができました。
次回に続きます。