【 Excel VBA 】探したい文字列が入っているセル列位置を取得したい

2No(@2No45519933)です。
取得する条件によって、ヘッダ項目が変わってしまうCSVファイルを編集する時に考えた関数になります。
はじめに
この関数を考えた経緯としては、ダウンロード条件によってヘッダ項目が増減してしまい、コピー位置指定を Columns (1)や Columns("A") などの固定値で指定するとコピー元やコピー先が変化してしまったため。
また、今回の場合検索したいヘッダ項目は必ずCSVファイルの中に含まれている条件だったので、文字列がなかった場合の処理は含んでいません…
あくまでも、列位置が固定ではなく変動しまってコピー位置などがずれてしまうから、文字列があるヘッダ項目の列位置を知りたい!って関数です。
やりたいこと
検索したい文字列を渡すと、その文字列が入った列番号を取得したい。
参考
https://www.relief.jp/docs/excel-vba-get-range-by-cells-property.html
列番号を探すVBAコード
検索対象の文字列と列番号を紐づけたオブジェクトの作成
サンプルなので、必要に応じて検索対象文字列などは変えてください!
検索範囲の設定
検索する範囲を定義します。
1 2 3 4 5 6 7 8 9 10 11 |
Sub sample() 'ワークシート定義 Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") '列の最終行数を定義 'A1のような形式ではなく、数値で定義 Dim lastCol as Long lastCol = 100 End Sub |
検索対象文字列と列番号が紐付いたオブジェクトを返す関数作成
キー(検索対象文字列)を渡すとアイテム(列番号)を返すようにしたいので、Dictionaryを使って連想配列で作成しました。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
'調べたいシートの名前と検索範囲用の最終列番号を渡すと '検索対象とその文字列がある列番号を完全一致で検索し、連想配列を作成 Function getHeadPosition(lastCol As Integer, findWs As Worksheet) As Object Dim headerCol As Object Set headerCol = CreateObject("Scripting.Dictionary") '検索値初期設定 headerCol.Add "item1", 0 headerCol.Add "item2", 0 headerCol.Add "item3", 0 Dim key As Variant Dim colNum As Integer colNum = 0 For Each key In headerCol '検索範囲から完全一致で検索 colNum = findWs.Range(findWs.Cells(1, 1), findWs.Cells(1, lastCol)).Find(key, LookAt:=xlWhole).Column headerCol(key) = colNum Next key Set getHeadPosition = headerCol End Function |
関数を使ってオブジェクトを作成
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Sub sample() 'ワークシート定義 Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") '列の最終行数を定義 'A1のような形式ではなく、数値で定義 Dim lastCol as Long lastCol = 100 'オブジェクトの作成 Dim position as Variant '最終行数と対象ワークシートを関数に渡す Set position = getHeadPosition(lastCol, ws) End Sub |
そうするとこんなデータを取得。
※ブレイクポイントを置くとオブジェクトの中身を確認できます
position Variant/Object/Dictionary
Count 3 Long
Item1 "item1" Variant/String
Item2 "item2" Variant/String
Item3 "item3" Variant/String
列番号取得
列番号を取得したい場合は、検索文字列を作成したオブジェクトに渡します
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Sub sample() 'ワークシート定義 Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") '列の最終行数を定義 'A1のような形式ではなく、数値で定義 Dim lastCol as Long lastCol = 100 'オブジェクトの作成 Dim position as Variant '最終行数と対象ワークシートを関数に渡す Set position = getHeadPosition(lastCol, ws) '列番号取得 'item1の列番号を探す場合、positionオブジェクトに"item1"を渡す Dim colNum as Integer colNum = position("item1") End Sub |
これでダウンロード条件によって列位置が変わっても、列番号を取得することができます。
現状検索したい文字列が固定なので、柔軟性にはかける部分はありますが、一応使える形にはなったのかなーと。
文字列がない場合どうするかも追記しないと予期せぬエラーを起こしそう。
いや、おきます…
そのことについて追々追記します。多分。
みなさま良いExcel VBAライフを!
それでは!
ディスカッション
コメント一覧
まだ、コメントがありません