【関数・プロパティ設定をつかう】

 7 文字列のループで1文字ずつ分解

 8 文字が入っているセルの最大行を取得

 9 文字が入っているセルの最大列を取得

10 行のループで複数の文字列を連続分解

11 列のループで複数の文字列を連続分解

12 指定した行番号と列番号のセル番地を取得

13 特定の文字列を指定文字列に置き換える

行のループで複数の文字列を連続分解

パイさん

「今回は、Excel VBA/マクロによって、

 Mid関数文字列のループ処理、更に、行のループ処理を組み合わせて、

 複数行の文字列を左端から右端まで順に1文字ずつ分解してみましょう

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

Img7_3_01

1. 指定行のセルの文字列を1文字ずつ分解

パイさん

「Excelの3行目に固定して、A列のセルから取得した文字列を左端から右端まで順に1文字ずつ分解した文字を、

 B列からM列までのセルに順に格納してみましょう。

 読込列:『ECm』をA列つまり『1』列、読込行:『ERw』を『3』行目、最大文字数を変数『MaxStr』、

 書込列を変数『WCm』、開始位置を変数『StP』としてから、プログラムコードを考えてみましょう。」

ソンくん

「『ECm』を『1』、『ERw』を『3』として定数宣言、

 『MaxStr』・『WCm』・『StP』を整数型として変数宣言。

 Const ECm = 1

 Const ERw = 3

 Dim MaxStr As Integer

 Dim WCm As Integer

 Dim StP As Integerと記述します。

 

 先ず、A列のセルの値から最大文字数を取得します。

 MaxStr = Len(Cells(ERw, ECm).Value)

 

 1文字目から最大文字数まで1文字ずつカウントアップしていきながら、

 そのつどA列のセルの値を1文字ずつ読みとり、セル(REw, WCm)に書きこみます。

 For StP = 1 To MaxStr

   WCm = StP + 1

   Cells(ERw, WCm).Value = Mid(Cells(ERw, ECm).Value, StP, 1)

 Next StP

 と記述します。」

Sub 指定行_1文字連続分解()

 Const ECm = 1

 Const ERw = 3

 Dim MaxStr As Integer

 Dim WCm As Integer

 Dim StP As Integer

 

 MaxStr = Len(Cells(ERw, ECm).Value)

 

 For StP = 1 To MaxStr

   WCm = StP + 1

   Cells(ERw, WCm).Value = Mid(Cells(ERw, ECm).Value, StP, 1)

 Next StP

End Sub

パイさん

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

Img7_3_12

ソンくん

「2列目(B列)から11列目(K列)までのセルに『セ』『ル』『に』『文』『字』『を』『書』『き』『こ』『む』が、

 書きこまれました。」


2. 行のループでセルの文字列を1文字ずつ連続分解

パイさん

「Excelの3行目から12行目にかけて、A列のセルから取得した文字列を

 左端から右端まで順に1文字ずつ分解した文字を、B列からM列までのセルに順に格納してみましょう。

 読込列:『ECm』をA列つまり『1』列、

 最小行:『MinRw』を『3』行目、

 最大行:『MaxRw』を『12』行目、

 読込行を変数『ERw』、

 最大文字数を変数『MaxStr』、

 書込列を変数『WCm』、

 開始位置を変数『StP』としてから、プログラムコードを考えてみましょう。」

ソンくん

「『ECm』を『1』、『MinRw』を『3』、『MaxRw』を『12』として定数宣言、

 『ERw』・『MaxStr』・『WCm』・『StP』を整数型として変数宣言。

 Const ECm = 1

 Const MinRw = 3

 Const MaxRw = 12

 Dim ERw As Integer

 Dim MaxStr As Integer

 Dim WCm As Integer

 Dim StP As Integerと記述します。」


パイさん

「ほぼOKなんですが、一点アドバイスします。


 読込行:『ERw』を整数型『Integer』として変数宣言していますが、

 整数型『Integer』では -32,768~32,767の範囲の数しか格納できません。

 つまり、『Integer』では 32,767行までしか格納できません。


 しかし、Excelの最大行数は、32,767行より、はるかに多いです。

 Excel2003までは、最大行数=65,536行だったのですが、

 Excel2007からは、最大行数=1,048,576行となっています。

 そのため、もしマクロでExcelの行数が32,767行を超えるようなデータを扱うときは、

 『Integer』ではデータを格納できなくなってしまいます。


 だけど、長整数型『long』なら -2,147,483,648~2,147,483,647の範囲まで格納できます。

 つまり、long』なら 2,147,483,647行まで格納できます。


 Excelの行数が32,767行を超えるようなケースもあるかもしれないので、

 に関して変数宣言するときは、長整数型『long』をつかうことをお薦めします。」


ソンくん

「わかりました。読込:『ERw』は、長整数型『long』として変数宣言します。

 Dim ERw As long

 と記述します。」

パイさん

「次に、A列のセルの値から最大文字数を取得します。

 その後、1文字目から最大文字数まで1文字ずつカウントアップしていきながら、

 そのつどA列のセルの値を1文字ずつ読みとり、セル(ERw, WCm)に書きこみます。

 MaxStr = Len(Cells(ERw, ECm).Value)

 

 For StP = 1 To MaxStr

   WCm = StP + 1

   Cells(ERw, WCm).Value _

    = Mid(Cells(ERw, ECm).Value, StP, 1)

 Next StP

 このプログラムコードの集まりを【ブロックA】とします。


 それから、Excelの最小行:『MinRw』(3行目)から最大行:『MaxRw』(12行目)にかけて、

 各行の処理【ブロックA】ループ処理で実行します。


 そこで、【ブロックA】をはさむように、

 【ブロックA】の前後にFor ERw = MinRw To MaxRwNext ERw

 追加します。

 For ERw = MinRw To MaxRw

   【ブロックA】

 Next ERw

 と記述します。」

Sub 行ループ_1文字連続分解()

 Const ECm = 1

 Const MinRw = 3

 Const MaxRw = 12

 Dim ERw As long

 Dim MaxStr As Integer

 Dim WCm As Integer

 Dim StP As Integer

 

 For ERw = MinRw To MaxRw

   MaxStr = Len(Cells(ERw, ECm).Value)

   For StP = 1 To MaxStr

     WCm = StP + 1

     Cells(ERw, WCm).Value _

      = Mid(Cells(ERw, ECm).Value, StP, 1)

   Next StP

 Next ERw

End Sub

パイさん

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

Img7_3_22

ソンくん

「Excelの3行目から12行目にかけて、A列のセルから取得した文字列を左端から右端まで

 順に1文字ずつ分解した文字が、2列目(B列)から13列目(M列)までのセルに順に書きこまれました。」


3. 最大行の取得後、行のループでセルの文字列を1文字ずつ連続分解

パイさん

「予め、次のようなExcelシートを用意します。

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

 A列のセルから取得した文字列を左端から右端まで順に1文字ずつ分解した文字を、

 B列からM列までのセルに順に格納してみましょう。

 読込列:『ECm』をA列つまり『1』列、最小行:『MinRw』を『3』行目、

 最大行を変数『MaxRw』、読込行を変数『ERw』、最大文字数を変数『MaxStr』、

 書込列を変数『WCm』、開始位置を変数『StP』としてから、ソースコードを考えてみましょう。」

Img7_3_41

ソンくん

「『ECm』を『1』、『MinRw』を『3』として定数宣言、

 『MaxRw』・『ERw』を長整数型として変数宣言。

 『MaxStr』・『WCm』・『StP』を整数型として変数宣言。

 Const ECm = 1

 Const MinRw = 3

 Dim MaxRw As long

 Dim ERw As long

 Dim MaxStr As Integer

 Dim WCm As Integer

 Dim StP As Integerと記述します。


 先ず、ExcelのA列の文字が入っているセルの最大行を取得します。

 MaxRw = Range("A65536").End(xlUp).Row

 

 次に、A列のセルの値から最大文字数を取得します。

 その後、1文字目から最大文字数まで1文字ずつカウントアップしていきながら、

 そのつどA列のセルの値を1文字ずつ読みとり、セル(ERw, WCm)に書きこみます。

 MaxStr = Len(Cells(ERw, ECm).Value)

 

 For StP = 1 To MaxStr

  WCm = StP + 1

  Cells(ERw, WCm).Value _

   = Mid(Cells(ERw, ECm).Value, StP, 1)

 Next StP

 このプログラムコードの集まりを【ブロックB】とします。


 それから、Excelの最小行:『MinRw』(3行目)から最大行:『MaxRw』(12行目)にかけて、

 各行の処理【ブロックB】ループ処理で実行します。

 For ERw = MinRw To MaxRw

   【ブロックB】

 Next ERw

 と記述します。」

Sub 最大行取得後_行ループ_1文字連続分解()

 Const ECm = 1

 Const MinRw = 3

 Dim MaxRw As long

 Dim ERw As long

 Dim MaxStr As Integer

 Dim WCm As Integer

 Dim StP As Integer

 

 MaxRw = Range("A65536").End(xlUp).Row

 

 For ERw = MinRw To MaxRw

   MaxStr = Len(Cells(ERw, ECm).Value)

   For StP = 1 To MaxStr

     WCm = StP + 1

     Cells(ERw, WCm).Value _

      = Mid(Cells(ERw, ECm).Value, StP, 1)

   Next StP

 Next ERw

End Sub

パイさん

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

Img7_3_43

ソンくん

「Excelの3行目から12行目にかけて、A列のセルから取得した文字列を2列目(B列)から13列目(M列)までのセルに

 左端から右端まで順に1文字ずつ分解した文字が、

 順に書きこまれました。」


パイさん

「このように、Mid関数文字列のループ処理、更に、行のループ処理を組み合わせると、

 複数行の文字列を左端から右端まで順に1文字ずつ分解することができます。」



目 次

1. セルに文字を書きこむ
セルに文字を書きこむ

2. RangeとCellsでセルの位置を指定する
RangeとCellsでセルの位置を指定する

3. ループ処理でセルに値を書きこむ
ループ処理でセルに値を書きこむ

4. ループ処理でセルの値を読みとる
ループ処理でセルの値を読みとる

5. ループ処理でセルからセルへ値を代入する
ループ処理でセルからセルへ値を代入する

6. 条件分岐処理でセルの操作を分ける
条件分岐処理でセルの操作を分ける

7. 関数・プロパティ設定をつかう
関数・プロパティ設定をつかう

8. 他のレンジ/シート/ブックを更新する
他のレンジ/シート/ブックを更新する




YouTube

Excelマクロの実用版とデモ版


ブログ