VBA学習ツール無料配布！#22 テーマ:vbaでcollectionにadd！

Option Explicit

Sub correctAnswerExample()
    Dim readWriteSht As Worksheet: Set readWriteSht = ThisWorkbook.Worksheets("読み込み＆書き込みシート")
    Dim colTbl As ListObject: Set colTbl = readWriteSht.ListObjects("collectionテーブル")
    Dim colData As New Collection
    Dim selectedKey As String: selectedKey = readWriteSht.Cells(6, 5).Value
    Dim loopLow As ListRow
    ' テーブルから「キー」, 「値」をコレクションに追加していく(For Each文でループ)
    For Each loopLow In colTbl.ListRows
        colData.Add Item:=loopLow.Range.Cells(1, 2).Value, Key:=loopLow.Range.Cells(1, 1).Value
    Next
    readWriteSht.Cells(9, 5).Value = colData(selectedKey)
    ' シート「読み込み＆書き込みシート」をアクティベートにする
    readWriteSht.Activate
    ' MsgBox関数で「実行完了」と表示
    MsgBox ("実行完了")
End Sub