縦から横か､それとも横から縦か（テーブルの値取得）。

縦から横か､それとも横から縦か｡
f:id:Infoment:20191001211555p:plain
先日の社内勉強会で、テーブル（表）内の特定セルの値を取り出す操作があった。その時のお話から抜粋。例えば､このようなテーブルがあるとする｡

f:id:Infoment:20191001211623p:plain

この中で､みかんの単価が知りたいとき､どうすればよいか｡このとき､縦から横か､それとも横から縦かという話になった｡

まず､縦から横､つまり列方向でみかんを探してから､当該行の単価を取得する場合を考える｡

品名列を「みかん」で検索。
該当セルの単価を取得。

コードにすると､こんな感じだ｡今回は行番号が欲しいので､
FindではなくMatchを用いている｡

Sub 縦から横()
    Dim Tb As ListObject
    Set Tb = ActiveSheet.ListObjects(1)
    Dim MatchResult As Long
        MatchResult = WorksheetFunction.Match("みかん", Tb.ListColumns("品名").DataBodyRange)
    Dim UnitPrice As Double
        UnitPrice = Tb.ListRows(MatchResult).Range.Cells(Tb.ListColumns("単価").Index)
        Debug.Print UnitPrice
End Sub

一方で､横から縦､つまりまず単価の列を指定して､その中でみかんの行を指定する場合を考える｡

単価の列をデータ範囲として取得。
その中の、みかんの行の値を取得。

コードにすると､こんな感じだ｡先ほどより､ちょっとだけ短い｡

Sub 横から縦()
    Dim Tb As ListObject
    Set Tb = ActiveSheet.ListObjects(1)
    Dim MatchResult As Long
        MatchResult = WorksheetFunction.Match("みかん", Tb.ListColumns("品名").DataBodyRange)
        
    ' 先ほどと違うところ。
    Dim UnitPrice As Double
        UnitPrice = Tb.ListColumns("単価").DataBodyRange(MatchResult)
        Debug.Print UnitPrice
End Sub

　
幸い今回は検索文字が表の一番左にあるため､Vlookupを使うという方法もある｡

Sub VLookupで()
    Dim Tb As ListObject
    Set Tb = ActiveSheet.ListObjects(1)
        
    Dim UnitPrice As Double
        UnitPrice = WorksheetFunction.VLookup("みかん", Tb.DataBodyRange, Tb.ListColumns("単価").Index, False)
        Debug.Print UnitPrice
End Sub

　
辞書を使う方法も､あるだろう｡ひととおりデータを取得してから､該当するものを取り出す方法だ｡今回は豪勢に､Enumまで使ってしまおう｡

Enum 列名
    品名 = 1
    数量
    単価
    合計
End Enum

Sub 辞書()
    Dim Tb As ListObject
    Set Tb = ActiveSheet.ListObjects(1)
    
    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")
    
    Dim ListRow As Excel.ListRow
        For Each ListRow In Tb.ListRows
            With ListRow.Range
                Dict(.Cells(列名.品名).Value) = .Cells(列名.単価).Value
            End With
        Next
        Debug.Print Dict("みかん")
End Sub