VBA セルにSUM関数を相対参照で埋め込む時に変数を使用したい

質問

VBAを使用して、セルに相対参照でSUM関数を仕込もうと考えています。

Cells(goukei_start_row, 10) = "=SUM(R11C10:R" & goukei_start_row - 1 & "C" & 10 & ")"

上記でやると、絶対参照で　=SUM($J$11:$J$43)　となってしまうのですが、
$を付けず=SUM(J11:J43)のような形にするにはどうすればよいのでしょうか？

回答No.1

f272 · ベストアンサー

こんな感じ？
Cells(goukei_start_row, 10) = "=SUM(R[" & 11 - goukei_start_row & "]C[0]:R[-1]C[0])"

お礼

できました！ありがとうございます。
でもなぜこれでできるのだろう・・・。勉強してみます。

回答No.3

Wendy02

お返事ありがとうございます。

>i = 44　を、先に下辺セルの行を変数に代入することで出来ると思うので、試してみたいと思います

これは、お分かりだと思いますが、

i =Range("J1000").End(xlUp).Offset(1).Row

入力行が決まります。(本来は、65536やRows.Countから、Endプロパティで、データ行のあるところまで探します）

必ずしも、ご要求どおりにはなってはいないと思いますが、R1C1とA1 の考え方が違いますから、もしかしたら、すぐに理解できないかもしれません。

R11C:R[-1]C だけで済むというのを理解できないかもしれませんが、いろいろ試して、A1式で数式が変わることを納得してもらうしかありません。

回答No.2

Wendy02

こんばんは。

最初に、昔、私も「goukei_start_row」のような変数の書き方をしていたことがありますが、ものすごく修正しにくくなりましたので、あまりそのような書き方をしないほうがよいです。インクリメンタルな変数は、i, j, k　また、n,m など。また、Row を取って、rw とすれば十分です。今では流行りませんが、lngRow とかいう方法もあります。ブロパティ名や関数名そのものは使わないほうがよいです。×Row, Sum, Str
　私は、「goukei_start_row」という書き方は、ある参考本（九天社）にあったような気がしますが、一度でやめました。

次に、プロパティはつけましょう。ここでは、Formula でよいと思います。FormulaR1C1 は必要ありません。ただし、ローカル（日本版独特=日付式など）の数式が必要なときがあるので、FormualLocal も良いです。

マクロで合計の数式を入れる場合は、
>$を付けず=SUM(J11:J43)のような形にするにはどうすればよいのでしょうか？
ではなくて、本来は、=SUM(J$11:J43) のような形にするのが良いです。そうすると、数式はいつも同じです。

このマクロを試してみてください。

Sub Test1()
　　Dim i As Long
　　i = 44
　　If i > 11 Then
　　　　Cells(i, 10).Formula = "=SUM(R11C:R[-1]C)"
　　End If
End Sub

なお、数式の本来の意味が、こういうことでなかったら、見過ごしてください。

お礼

ご回答ありがとうございます。
そうですね、後で見たときにわかりやすくと思ってつけていたのですが、
短くする方のメリットも大きいですし、次に組むときは簡単なものにしたいと思います。

数式に関しては、SUMの上辺（*11）は一定ですが、下辺(*43)は一定ではないのですが、
　i = 44　を、先に下辺セルの行を変数に代入することで出来ると思うので、試してみたいと思います！