計算式を埋め込む

---

抽出した結果に計算式を埋め込んでみましょう。

Sheet1 に入力されているリストから「商品コード=A001」のデータだけを Sheet2 に抽出し、「受注数」の合計を最下行に表示します。「受注数」の数値を VBA で計算して結果だけを表示することもできますが、ここでは最下行に SUM 関数を埋め込む方法をご紹介します。抽出する部分のコードは Sample03_2 をそのまま使えますので Call しちゃいましょう。

Sub Sample04()
    Call Sample03_2
    With Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)
        .Offset(1, 0) = "合計"
        .Offset(1, 2).FormulaLocal = "=SUM($C$2:" & .Offset(0, 2).Address & ")"
    End With
End Sub

合計の計算式を最下行に埋め込むには、まず最下行が何行目かを調べなければなりません。これには古典的かつ有効な方法があります。End ステートメントです。Excel の End モードを利用して、ワークシートの最下行から上方向にジャンプします。End ステートメントの返り値はデータが入力されている最下行になりますので、Offset プロパティで 1 つ下のセルや右隣のセルを取得します。

ここでは「=SUM($C$2:$C$5)」のように絶対参照で SUM 関数を作成しましたが、相対参照にしたいのでしたら最後のコードを

.Offset(1, 2).FormulaLocal = "=SUM(C2:" & .Offset(0, 2).Address(0, 0) & ")"

とします。

【追記(2018年12月)】

ああ、だめだ、読んでると(昔の自分に)突っ込みを入れたくなる(笑)。上記のコードで、セルに数式を代入するときFormulaLocalプロパティを使っていますが、これはValueプロパティでOKです。セルに代入されている数式を取得するには、Formulaプロパティなどを使いますが、手動操作でセルに数式を代入するとき、特に変わったことをするわけではなく、ただ「=」で始まる文字列を入力するだけです。"合計"や"田中"を入力するように。であれば、VBAで数式を代入するときも同じように考えるべきです。さらにセルのアドレスを文字列結合作っているあたりも恥ずかしいです。まさか自分が、こんな下品なコードを書いていたとは(泣)。ここは、次のようにすべきですね。マクロは動けばいいのではありません(自分に言ってる)。

Sub Sample04()
    Call Sample03_2
    With Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)
        .Offset(1, 0) = "合計"
        .Offset(1, 2) = "=SUM(" & Range(Range("C2"), Cells(Rows.Count, 3).End(xlUp)).Address & ")"
    End With
End Sub