VBAでMatch関数を使用し列番号を取得する

4615 ワード
match VBA マクロ Excel 関数 Excel テキストリンク

列挿入や列削除で列番号が変わる可能性がある

Excelのシートでは列を追加したり削除したりすることがあり
以下のようなコードだとその度書き直すことになるので

ThisWorkbook.Worksheets("Sheet1").Cells(LastRow + 1, 1).Value = 10

その度書き直さなくてもいいように
項目行から項目名をMatch関数で検索し、変数に格納します。

Sub MasterDataAdd1()'最終行のひとつ下にデータを追加するコード
    Dim NoCol As Long 'Noの列番号を格納する変数
    Dim IDCol As Long 'IDの列番号を格納する変数
    Dim LastRow As Long '最終行を格納する変数

'列番号を変数に格納する
'Masterはシートオブジェクト名です
    NoCol = WorksheetFunction.Match("No", Master.Rows(1), 0) 'Match関数で列数格納
    IDCol = WorksheetFunction.Match("ID", Master.Rows(1), 0) 'Match関数で列数格納
    LastRow = Master.Cells(Rows.Count, NoCol).End(xlUp).Row '最終行格納
    Master.Cells(LastRow + 1, NoCol).Value = 10 '最終行のひとつ下にNo追加
    Master.Cells(LastRow + 1, IDCol).Value = "jjj" '最終行のひとつ下にID追加
End Sub

実行結果

ワタシ流こだわり

列番号を格納する変数は「～Col」
行番号を格納する変数は「～Row」

ちなみに

このままだと項目行の上に行を追加されたり項目名を変更されたらアウトです。
その可能性がある場合はその対策もとる
でもとりあえずむやみな変更をしないように伝えておくのは大事。