結合しているセルに代入する

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

これ、先日のセミナーで、吉岡さんから質問された内容です。いやぁ、実務ってのは、いろんなことを求められるんだなと痛感しました。セミナーでは、マクロを作るにあたって「まず、どう考えるか」から「どうやって作っていくか」という"考え方"と"作り方"の過程を詳しく解説しましたが、ここでは主に"考え方"を解説します。

元データは普通のリストです。たとえば下図のような。

ここから各セルのデータを、下図のように"結合されているセル"にコピーしていきたいと。そういう質問でした。

揚げ足を取るつもりはありませんけど、これ「コピーしたい」って考えていたら作れません。だって、コピーできませんから。やってみましょうか。セル範囲A1:C1を、セルE1にコピーすると、こうなります。

結合が解除されちゃいます。セルの結合というのは、セルの書式ですから当然の結果です。このように、セルをコピーすると結合が解除されちゃうのですから、どうしてもこの作業を「セルのコピー」でやるのなら、セル結合が解除されたセル範囲に対して、もう一度セル結合してやるという二度手間になります。これは、いただけません。なので、今回の作業は「セルのコピー」ではなく「セルの値を代入する」という操作です。両者の違いを正確に認識する必要があります。くれぐれも、今回やりたいことを「データを"引っ張ってくる"」などとは考えないでください。Excelに"引っ張ってくる"という機能は存在しません。"コピー"なのか"代入"なのか、どちらの方針でスタートするのかが最も重要です。試しにやってみましょう。

Sub Macro1()
    Range("E1").Value = Range("A1").Value
End Sub

セルのコピーではなく、セルの値を代入すれば、セルの結合は解除されません。とはいえ、これを1セルずつやるのは大変ですから、1行分の値を一気に代入しましょう。

Sub Macro2()
    Range("E1:G1").Value = Range("A1:C1").Value
End Sub

うまくいきました。ここから、少しずつ発展させていきます。上記のコードでは、まず1行目のセル範囲A1:C1だけを扱いました。これを、1行目→2行目→…→最終行 と、最後までやってやればいいんです。For Nextの繰り返しを使います。となると、Range("E1:G1")という書き方ではできません。ここは「Range(左セル, 右セル)」という書き方をします。

Sub Macro3()
    Dim i As Long
    For i = 1 To 5
        Range(Cells(■, "E"), Cells(■, "G")).Value = Range(Cells(★, "A"), Cells(★, "C")).Value
    Next i
End Sub

これで、変数iを変化させればできそうです。ああ、くれぐれも「Range("E" & ★ & ":G" & ★)」などという下品な書き方はしないでくださいね。あと、今回のように複数セルの値を一括代入するときは、代入元(イコール記号の右側)のValueは省略できません。VBAの仕様です。なぜかという理由は「RangeオブジェクトとValueプロパティと配列の関係」による話なので、考えない方がいいです。そういうものだ、と飲み込んでください。さて、代入元にValueをつけるのですから、受取側にもつけてバランスを取りましょう。

とりあえず今回は、話を簡単にするため、最終行を"5行目"と限定します。実務では最終行が分からないでしょうから、ここはEndモードなどを使ってください。

さて、本題はここからです。上記の考え方までで、今われわれは「1→2→3→4→5」と変化する変数iを得ることができました。この変数iは、言うまでもなく、イコール記号の右側(代入元)で使います。★の部分です。

Sub Macro4()
    Dim i As Long
    For i = 1 To 5
        Range(Cells(■, "E"), Cells(■, "G")).Value = Range(Cells(i, "A"), Cells(i, "C")).Value
    Next i
End Sub

イコール記号の右側(代入元)の変数iが「1→2→3→4→5」と変化します。ここで、

  • 受取側の行番号は 1 ← 代入元の変数iが 1 のとき
  • 受取側の行番号は 3 ← 代入元の変数iが 2 のとき
  • 受取側の行番号は 5 ← 代入元の変数iが 3 のとき
  • 受取側の行番号は 7 ← 代入元の変数iが 4 のとき
  • 受取側の行番号は 9 ← 代入元の変数iが 5 のとき

となるような仕組みを考えます。つまり、われわれに与えられた唯一の武器である「変数i」だけを使って、「1→3→5→7→9」と変化するような計算式を作ります。要するにルールを考えるのですが、私の経験では、こういうとき先頭と最後には"イレギュラー"なケースが多いです。あ、私感です。なので私は、こういうとき"真ん中へん"のルールから当たりをつけます。この、"当たりをつける"というのは、何よりも経験が物を言いますね。さて、変数iが2～4あたりを見ると、何となく「2倍した数に近いな」って感じます。

  • 受取側の行番号は 1 ← 変数iが 1 のとき
  • 受取側の行番号は 3 ← 変数iが 2 のとき → 2倍する → 4
  • 受取側の行番号は 5 ← 変数iが 3 のとき → 2倍する → 6
  • 受取側の行番号は 7 ← 変数iが 4 のとき → 2倍する → 8
  • 受取側の行番号は 9 ← 変数iが 5 のとき

今回は簡単ですね。変数iを2倍した数から1を引けば求められそうです。

  • 受取側の行番号は 1 ← 変数iが 1 のとき
  • 受取側の行番号は 3 ← 変数iが 2 のとき → 2倍する → 4 → 1を引く → 3
  • 受取側の行番号は 5 ← 変数iが 3 のとき → 2倍する → 6 → 1を引く → 5
  • 受取側の行番号は 7 ← 変数iが 4 のとき → 2倍する → 8 → 1を引く → 7
  • 受取側の行番号は 9 ← 変数iが 5 のとき

いけそうなので、先頭と最後も確認します。

  • 受取側の行番号は 1 ← 変数iが 1 のとき → 2倍する → 2 → 1を引く → 1
  • 受取側の行番号は 3 ← 変数iが 2 のとき → 2倍する → 4 → 1を引く → 3
  • 受取側の行番号は 5 ← 変数iが 3 のとき → 2倍する → 6 → 1を引く → 5
  • 受取側の行番号は 7 ← 変数iが 4 のとき → 2倍する → 8 → 1を引く → 7
  • 受取側の行番号は 9 ← 変数iが 5 のとき → 2倍する → 10 → 1を引く → 9

このようにルールを作ったら、ここで「この2や1って何のことだろう？」って考えるといいです。1はともかくとして、2というのは「たぶん、結合セルの行数じゃね？」みたいに。はずれてもいいです。そう考えると、後が楽になります。

とりあえず今回は、次のようにすればできると分かりました。

Sub Macro5()
    Dim i As Long
    For i = 1 To 5
        Range(Cells(i * 2 - 1, "E"), Cells(i * 2 - 1, "G")).Value = Range(Cells(i, "A"), Cells(i, "C")).Value
    Next i
End Sub

質問のケースでは、とりあえずできましたけど、ここはひとつ、いろいろなケースにも対応できるよう、普遍的な仕組みを検討しましょう。まずは、先に当たりをつけた「2倍の2って、結合しているセルの行数なのかな？」と。やってみましょう。

上図のように、今度は3行が結合されています。ここで作成したいルールは次のとおりです。

  • 受取側の行番号は 1 ← 代入元の変数iが 1 のとき
  • 受取側の行番号は 4 ← 代入元の変数iが 2 のとき
  • 受取側の行番号は 7 ← 代入元の変数iが 3 のとき
  • 受取側の行番号は 10 ← 代入元の変数iが 4 のとき
  • 受取側の行番号は 13 ← 代入元の変数iが 5 のとき

先と同じ計算でやってみます。

  • 受取側の行番号は 1 ← 変数iが 1 のとき → 3倍する → 3 → 1を引く → 2
  • 受取側の行番号は 4 ← 変数iが 2 のとき → 3倍する → 6 → 1を引く → 5
  • 受取側の行番号は 7 ← 変数iが 3 のとき → 3倍する → 9 → 1を引く → 8
  • 受取側の行番号は 10 ← 変数iが 4 のとき → 3倍する → 12 → 1を引く → 11
  • 受取側の行番号は 13 ← 変数iが 5 のとき → 3倍する → 15 → 1を引く → 14

うまくいきません。てゆーか、1ずれてます。これ、どうやら「1を引く」ではなく、今回は「2を引く」としなければいけないようです。

  • 受取側の行番号は 1 ← 変数iが 1 のとき → 3倍する → 3 → 2を引く → 1
  • 受取側の行番号は 4 ← 変数iが 2 のとき → 3倍する → 6 → 2を引く → 4
  • 受取側の行番号は 7 ← 変数iが 3 のとき → 3倍する → 9 → 2を引く → 7
  • 受取側の行番号は 10 ← 変数iが 4 のとき → 3倍する → 12 → 2を引く → 10
  • 受取側の行番号は 13 ← 変数iが 5 のとき → 3倍する → 15 → 2を引く → 13

じゃ、これ、セルが4行結合されてたらどうなるのでしょう。セルが結合されている画像は割愛しますが、次のようなルールが必要です。

  • 受取側の行番号は 1 ← 代入元の変数iが 1 のとき
  • 受取側の行番号は 5 ← 代入元の変数iが 2 のとき
  • 受取側の行番号は 9 ← 代入元の変数iが 3 のとき
  • 受取側の行番号は 13 ← 代入元の変数iが 4 のとき
  • 受取側の行番号は 17 ← 代入元の変数iが 5 のとき

計算は、こうなります。

  • 受取側の行番号は 1 ← 変数iが 1 のとき → 4倍する → 4 → 3を引く → 1
  • 受取側の行番号は 5 ← 変数iが 2 のとき → 4倍する → 8 → 3を引く → 5
  • 受取側の行番号は 9 ← 変数iが 3 のとき → 4倍する → 12 → 3を引く → 9
  • 受取側の行番号は 13 ← 変数iが 4 のとき → 4倍する → 16 → 3を引く → 13
  • 受取側の行番号は 17 ← 変数iが 5 のとき → 4倍する → 20 → 3を引く → 17

ここまでくれば、何となくお分かりですね。普遍的な計算式は、次のとおりです。

受取側の行番号 = 変数i×結合している行数ー(結合している行数ー1)

言うまでもありませんが、この計算式は"今回のケースに限っては"ということです。つまり「元データは1行目から始まっている」なおかつ「受取側の先頭は1行目から始まっている」という前提です。もしこれ、元データがたとえば5行目から始まっていたり、受取側の結合セルが4行目から始まっていたりすると、もう少し計算式を工夫しなければなりません。いろいろなケースを想定して、みなさんチャレンジしてみてくださいな。今回は、最も簡単なケースで、考え方を解説しました。