セル列番号を英字の列名に変換

パイさん

「Addressプロパティをつかうと、Cell(Row, Column)オブジェクトから

　Range(Cell1)オブジェクトのセル番地を取得できます。

　Replace関数をつかうと、対象となる文字列式で特定の文字列を指定した文字列に

　置き換えた文字列を取得できます。

　それでは、Excel VBA/マクロのAddressプロパティとReplace関数を組み合わせて、

　指定した列番号から英字の列名を取得してみましょう。」

パイさん

「サンプルデータとして次のようなExcelシートを用意します。」

1. セル列番号を英字の列名に変換する

パイさん

「Excelの1行目の文字が入っているセルの最大列を取得した後、

　Addressプロパティの引数、行参照に『False:相対参照』、列参照に『False:相対参照』を入れて、

　１行目のセル:Cells(1, 最大列番号)オブジェクトのセル番地を取得し、

　２行目のセル:Cells(2, 最大列番号)に格納してみましょう。

　最大列を変数『最大列』としてから、プログラムコードを考えてみましょう。」

ソンくん

「これは、以前やったことがある例題ですね。

　Dim 最大列 As Integer

　最大列 = Range("IV1").End(xlToLeft).Column

　Cells(2, 最大列).Value = Cells(1, 最大列).Address(False, False)

　ここで、マクロを実行します。 」

ソンくん

「ExcelのセルK2にセルK1のセル番地『K1』が書きこまれました。」

パイさん

「次に、２行目のセル:Cells(2, 最大列)に格納されたセル番地『K1』から、

　行番号『1』を取り除いた英字の列名を取得して、３行目のセル:Cells(3, 最大列)に格納してみましょう。

　『1』を取り除くということは、『1』を『』(ブランク)に置き換えるということです。

　『K1』で、『1』を、『』に、置き換えた英字の列名を取得して、３行目のセル:Cells(3, 最大列)に格納して

　みましょう。」

ソンくん

「Replace関数の

　第1引数:Expressionに対象となる文字列式としてCells(2, 最大列).Value、

　第2引数:Findに特定の文字列として『1』、

　第3引数:Replaceに指定した文字列として『』を入れてから、

　戻り値として、置き換えた文字列を取得し、Cells(3, 最大列) に格納します。

　Cells(3, 最大列).Value = Replace(Cells(2, 最大列).Value, "1", "")

　これで良いのでしょうか。」

パイさん

「その通りです。それでは、そのマクロを実行してみましょう。」

ソンくん

「Excelの２行目のセル:Cells(2, 11)に、１行目のセル:Cells(1, 11)のセル番地『K1』が書きこまれた後、

　セル番地『K1』から、『1』を取り除いた英字の列名『K』が、３行目のセル:Cells(3, 11)に書きこまれ

　ました。」

パイさん

「このように、Addressプロパティで取得したセル番地からReplace関数で行番号を取り除くことで、

　セル番地から英字の列名を取得することができます。」

2. 列のループでセル列番号を英字の列名に変換する

パイさん

「Excelの1行目の文字が入っているセルの最大列を取得した後、最小列から最大列にかけて、

　Addressプロパティの引数、行参照に『False』、列参照に『False』を入れて、

　１行目のセル:Cells(1, 列番号)オブジェクトのセル番地を取得し、

　２行目のセル:Cells(2, 列番号)に格納してみましょう。

　最小列:『最小列』を『2』列、列を変数『Cm』、最大列を変数『最大列』としてから、

　プログラムコードを考えてみましょう。」

ソンくん

「これは、以前やったことがある例題ですね。

　Const 最小列 = 2

　Dim Cm As Integer

　Dim 最大列 As Integer

　最大列 = Range("IV1").End(xlToLeft).Column

　For Cm = 最小列 To 最大列

　　　Cells(2, Cm).Value = Cells(1, Cm).Address(False, False)

　Next Cm

　ここで、マクロを実行します。 」

ソンくん

「ExcelのB列からK列にかけて、1行目のセルから取得したセル番地が、2行目のセルに順に

　書きこまれました。」

パイさん

「次に、最小列から最大列にかけて、２行目のセル:Cells(2, Cm)に格納されたセル番地『X1』から、

　『1』を取り除いた英字の列名を取得して、３行目のセル:Cells(3, Cm)に格納してみましょう。

　『X1』で、『1』を、『』に、置き換えた英字の列名を取得して、３行目のセル:Cell(3, Cm)に格納して

　みましょう。」

ソンくん

「Replace関数の第1引数:Expressionに対象となる文字列式としてCells(2, Cm).Value、

　第2引数:Findに特定の文字列として『1』、第3引数:Replaceに指定した文字列として『』を入れてから、

　戻り値として、置き換えた文字列を取得し、Cells(3, Cm) に格納します。

　Cells(3, Cm).Value = Replace(Cells(2, Cm).Value, "1", "")

　この処理をExcelの最小列:『最小列』(2列目)、最大列:『最大列』(11列目)にかけて、ループ処理で実行します。」

パイさん

「その通りです。それでは、そのマクロを実行してみましょう。」

ソンくん

「Excelの2列目(B列)から11列目(K列)にかけて、２行目のセルに１行目のセル番地『X1』が書きこまれた後、

　セル番地『X1』から、『1』を取り除いた英字の列名『X』が、３行目のセルに順に書きこまれました。」

パイさん

「次は、直接、AddressプロパティとReplace関数を組み合わせて、セル番地から、英字の列名を取得して

　みましょう。先ず、ExcelのセルB2からK3をクリアしてください。」

パイさん

「Excelの１行目の文字が入っているセルの最小列から最大列にかけて、Addressプロパティにより取得した、

　１行目のセル:Cells(1, 列番号)オブジェクトのセル番地『X1』から、『1』を取り除いた英字の列名を

　取得して、３行目のセル:Cells(3, Cm)に格納してみましょう。」

ソンくん

「先ほどのプログラムコードCells(2, Cm).Value = Cells(1, Cm).Address(False, False)と

　Cells(3, Cm).Value = Replace(Cells(2, Cm).Value, "1", "")を組み合わせます。

　Cells(3, Cm).Value = Replace(Cells(1, Cm).Address(False, False), "1", "")

　と記述します。」

パイさん

「その通りです。それでは、そのマクロを実行してみましょう。」

ソンくん

「ExcelのB列からK列にかけて、Excelの１行目のセル番地『X1』から『1』を取り除いた

　英字の列名『X』が、３行目のセルに順に書きこまれました。」

パイさん

「このように、For文(ループ処理)のブロックの中で、Addressプロパティで取得したセル番地から

　Replace関数で行番号を取り除くというコードを記述すれば、複数のセル番地から

　英字の列名を取得することができます。」