VBAでExcelのテーブル名と列名で値を取得する簡単な方法

  • URLをコピーしました!

テーブルと列名を指定してVBAで値を取得する方法を紹介します。1行目からレコード数まで順番に取得できます。ループでも使えて便利です。

目次

例1:○○テーブルの●●列のi番目の値を取得する


テーブル名:支店一覧
列名:支店名

Sub TableValueSample()
  dim aList as ListObject
  Set aList = Range("支店一覧").ListObject
  Dim i as Long
  For i To aList.ListColumns("支店名").DatabodyRange.Count
    Debug.print aList.ListColumns("支店名").DatabodyRange(i)
  Next
End sub

実行結果
 大阪支店
 京都支店
 兵庫支店

実行すると営業所一覧テーブルの列見出し 営業所名の値が1行目から、イミディエイトウインドウに出力されるはずです。

取得した値を使ってFor文でループ処理をすることができます。このサンプルでは分かりやすいように繰り返し表記していますが、重複する部分はWithを使って整理すると良いでしょう。

注意点

Activeworkbookが変わると名前の範囲が参照できなくなるためエラーが起きます。そのため実行直後にSetでListObjectをセットしています。

メンテナンスを考慮して項目名はトップで指定して、For文の中は変数にしておくのが良いかもしれません。ケースバイケースだと思います。

例2 テーブルを配列に入れる

列名で指定してループで処理をすることができます。
・Uboundでテーブルのレコード数を取得できます。
・Listの型はvariantを使います。
・ここでは列番号1,2としていますが部分はEnumを使うと分かりやすいでしょう。

例 一覧テーブルの1列目の値に○○が含まれていたら3列目の値を該当とする。

Dim aList as variant
aList = Activesheet.ListObjects("一覧").DatabodyRange
For i = 1 to Ubound(aList)
  If instr(aList(i,1),"○○") then
    aList(i,2).value = "該当"
 Else
    aList(i,2).value = "該当なし"
  end if
Next

テーブルを使うメリット

・レコード数の上限をCountで取得しているのでテーブルのレコード数が変わっても対応できます。
・元データをテーブルで指定しているので、周辺の行や列の挿入削除によってテーブルが移動した場合でもエラーがおきにくい。

なごみ

以上簡単ですが、調べても見当たらなかったので参考までにご紹介しました。この方法はcells(i,j)と比べて、項目名がコード内で分かるのがメリットです。
ひとつの参考にしてみてください。AccessでいうところのDlookup関数に近い感覚で使えるように、関数にしておくのも良いかもしれません。


手順が少なくてシンプルな以下の方法もあります。よろしければご覧ください。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!
目次