VBAでSUM関数をセルにセットするため引数の作り方を知りたい?

Question 53.6 Previous Next
詳細は こちら 500連発第2弾マクロ番号243の「SUMで合計を計算する」について質問させてください。

データ行数が不規則なファイルを扱う時、いつも有効データの最終行より2行空けた行(即ち3行目)に、列トータルを計算する数式(SUM関数)を設定する様にしたいのですが。
ActiveCell.SpecialCells(xlLastCell).Select
Row_LastCell = ActiveCell.Row
Row_TopCell = 3   *** 1~2行目は見出しなので計算対象外
MyColumn = 5      *** 例えばE列の列トータルを求めるものとします
計算対象範囲は、
   Range(Cells(Row_TopCell, MyColumn), Cells(Row_LastCell, MyColumn))
と記述出来るかと思います。 FormulaR1C1プロパティを使う場合、どの様に記述すればよいでしょうか。ご教授の程、よろしくお願い申し上げます。
Answer   Copyright (C) 2002.6.14 永井善王
500連発第2弾マクロ番号243のサンプルは、ワークシート関数のSUMを呼び出して合計を計算し、その答えをセルにセットするための下記のようなマクロです。
    合計する範囲 = "E9:E13"
    合計 = Application.WorksheetFunction.Sum(Range(合計する範囲))
    Range("J9").Value = 合計
ところが、ご質問は、そのサンプルの使い方ではなくて、セルにSUM関数をセットする方法をお尋ねかと思います。
もし、そうであるならば、ワークシート関数をマクロでセルにセットするために Formulaプロパティを使ったサンプルが 226、234、236番にありますので、むしろ、それを参考になさった方がよろしいかと思います。
SUM関数をセットし終わった状態
ですが、その方法については理解されていて、疑問点としては 取得した行、列番号をどのように組み合わせれば SUM関数を使った数式ができるのかを知りたいのかな、とも思います。

質問文から右図のとおりワークシートを再現してみました。
E8セル(R8C5セル)に SUM関数をセットしたいのでしょうか。

セルに関数をセットすると通常、数式バーに =SUM(E3:E5) のように表示されますが、右図では =SUM(R3C5:R5C5) と表示されています。どちらも同じ意味ですが、問題解決の鍵はここにあります。

おそらく、あなたは変数 MyColumn、Row_TopCell、Row_LastCell に取得された数字を SUM関数の引数、つまり ( )の中にどう書けばよいのかで悩まれているのではないでしょうか。
あまり難しく考えないで、数式バーに表示されたとおりにセル範囲を表す文字列を作り出します。そして、それを活用して SUM関数を文字列として完成させ、セルにセットすれば解決するかと思います。

次のマクロは、あなたが変数 MyColumn(対象列)に 5 を与えていますので、図の数式バーどおりの文字列を作り出しています。もしも、E と与えるか、あるいは 5 を E に変換するかすれば、=SUM(E3:E5) という文字列を作り出すことになります。
変数名は一般の方にも読みやすいように変更してあります。あなたは読みにくいかもしれませんが、我慢してください。
Sub SUM関数をセルにセットする()
    ActiveCell.SpecialCells(xlLastCell).Select
    最下行 = ActiveCell.Row
    最上行 = 3
    対象列 = 5
    セル範囲 = "R" & 最上行 & "C" & 対象列 & ":R" & 最下行 & "C" & 対象列
    数式 = "=SUM(" & セル範囲 & ")"
    Range(Cells(最下行 + 3, 対象列), Cells(最下行 + 3, 対象列)).FormulaR1C1 = 数式

Excel VBA Macro