ここまでシンプルにできるのか！複雑なセルの転記のタブー、RangeよりもCellsを使え。

【今回の問題】

このような表にデータを転記してください

今回の問題のソースはこちら↓

図解！ Excel VBAのツボとコツがゼッタイにわかる本 プログラミング実践編 立山秀利

6列3行のセルにデータを転記していきます。はて？どうする？

この表をパッと見せられた時、私の頭にはVlookupかOffsetしか頭に浮かびませんでした。
しかし、「Vlookupは使わないよ。」と出題者さんに言われて書いたコードがこちら。

Sub 転記2()
'店舗名の行を抽出
Dim i As Long, j As Long
Dim area As String
'シート名
area = Range("C2").Value
      j = 5
        For i = 5 To Cells(Rows.Count, 1).End(xlUp).Row
            If Range("C2").Value = Cells(i, 3) Then
    
            Sheets(area).Range("B" & j).Offset(1, 0).Value = Cells(i, 1)
            Sheets(area).Range("B" & j).Value = Cells(i, 2)
            Sheets(area).Range("B" & j).Offset(0, 2).Value = Cells(i, 4)
            Sheets(area).Range("B" & j).Offset(1, 2).Value = Cells(i, 5)
            Sheets(area).Range("B" & j).Offset(2, 2).Value = Cells(i, 6)
       j = j + 3
      
            End If
        Next
    
End Sub

3行後に次の項目が来るので、変数ｊを使って、3行飛ばさせる処理をIfの最後に入れています。
これ、最初はものすごく試行錯誤しました。
For i = 5 To Cells(Rows.Count, 1).End(xlUp).Row Step 3
を最初に思い浮かべたのですが、これではうまくいかない。

For iの処理を終えた時点で、3行移動するのだから、For iの処理の最後に、記入する行頭 j に3を足してあげればいいんだ！と気づいたのは、やはりF8でひとつひとつプログラムを実行してからのことでした。

とりあえず、コードを書き終えたら、いきなりF5ではなくF8で、それぞれの処理が、意図したとおり行われているか確認しながらプログラムを動作させてみてください。

もちろん、コードを書きながら、実際にどのように動作するのか、頭の中でシュミレーションできていることが肝心です。頭の中のシュミレーションとF8でひとつづつ動かしたときの動作が同じかどうか。これで、おかしな点があれば、どこがまずいかだいたいわかります。

実はRangeを使わずにシンプルに書けるんです

最初のコードを出題者さんに提出した後、「Rangeは使わないよ。」といわれて頭をひねりました。
そこで目を付けたのが、そもそも列は固定されているし、Offsetも j を基準に動いている！という点。
ならばRange("B"&j)からオフセットしなくても、Cells(j行, 固定の列)でいけてしまいますよね。
というわけで、ここまでコードをシンプルにすることができました。

Sub 転記2()
'店舗名の行を抽出
Dim i As Long, j As Long
Dim area As String
'シート名
area = Range("C2").Value
      j = 5
        For i = 5 To Cells(Rows.Count, 1).End(xlUp).Row
            If Range("C2").Value = Cells(i, 3) Then
    
            Sheets(area).Cells(j + 1, 2) = Cells(i, 1)
            Sheets(area).Cells(j, 2) = Cells(i, 2)
            Sheets(area).Cells(j, 4) = Cells(i, 4)
            Sheets(area).Cells(j + 1, 4) = Cells(i, 5)
            Sheets(area).Cells(j + 2, 4) = Cells(i, 6)
       j = j + 3
      
            End If
        Next
    
End Sub

できるかぎりシンプルに

コードがだらだらと長くなってしまったときは次のことに気を付けてみてください。

• 無駄にRangeを使っていないか

• 変数や定数に置き換えてシンプルにできる部分はないか

• Withを使ってまとめられる部分はないか

シンプルなコードの何がいいかって、あとで見た時に、長いだらだらと書いたコードよりも、動作の把握がしやすいんですね。
メンテナンスのときに作業効率がぐっと上がるのです。
さらに、それぞれの動作について　'コメント　を書いておくのも、プログラムの可読性を上げるポイントです。