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

2019年2月8日

2No(@2No45519933)です。

取得する条件によって、ヘッダ項目が変わってしまうCSVファイルを編集する時に考えた関数になります。

1. はじめに
2. やりたいこと
3. 列番号を探すVBAコード

はじめに

この関数を考えた経緯としては、ダウンロード条件によってヘッダ項目が増減してしまい、コピー位置指定を Columns (1）や Columns("A") などの固定値で指定するとコピー元やコピー先が変化してしまったため。

また、今回の場合検索したいヘッダ項目は必ずCSVファイルの中に含まれている条件だったので、文字列がなかった場合の処理は含んでいません…

あくまでも、列位置が固定ではなく変動しまってコピー位置などがずれてしまうから、文字列があるヘッダ項目の列位置を知りたい！って関数です。

やりたいこと

検索したい文字列を渡すと、その文字列が入った列番号を取得したい。

参考
https://www.relief.jp/docs/excel-vba-get-range-by-cells-property.html

列番号を探すVBAコード

検索対象の文字列と列番号を紐づけたオブジェクトの作成

サンプルなので、必要に応じて検索対象文字列などは変えてください！

検索範囲の設定

検索する範囲を定義します。

Sub sample()
    'ワークシート定義
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    '列の最終行数を定義
    'A1のような形式ではなく、数値で定義
    Dim lastCol as Long
    lastCol = 100

End Sub

Sub sample()

'ワークシート定義

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Sheet1")

'列の最終行数を定義

'A1のような形式ではなく、数値で定義

Dim lastCol as Long

lastCol = 100

End Sub

検索対象文字列と列番号が紐付いたオブジェクトを返す関数作成

キー（検索対象文字列）を渡すとアイテム（列番号）を返すようにしたいので、Dictionaryを使って連想配列で作成しました。

'調べたいシートの名前と検索範囲用の最終列番号を渡すと
'検索対象とその文字列がある列番号を完全一致で検索し、連想配列を作成
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

'調べたいシートの名前と検索範囲用の最終列番号を渡すと

'検索対象とその文字列がある列番号を完全一致で検索し、連想配列を作成

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

関数を使ってオブジェクトを作成

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

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

列番号取得

列番号を取得したい場合は、検索文字列を作成したオブジェクトに渡します

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