【VBA】絶対セル参照の記述

質問

早速質問させていただきます。
ActiveCell.FormulaR1C1 = "=SUM(R2C:R[-1]C)"のように記述すると、=sum(A$2:(関数を記入する1つ上))のように、左側部分が行固定になってしまいます。行固定をしないで記述する方法はありますか？sum関数を入れるセルからの相対参照は、データの総数が都度変化しますのでできません。
よろしくお願いします。

回答No.4

Wendy02 · ベストアンサー

こんにちは。

例えば、

数値の最後尾を探す関数

1行目にSUM関数を置く場合の相対参照式

A1方式
=SUM(OFFSET(A2,,,MATCH(10^16,A2:A100,1)))

これは、相対参照式ですから、A列に書けば、上記のようになりますが、B列に書けば、
=SUM(OFFSET(B2,,,MATCH(10^16,B2:B100,1)))

のようになります。

R1C1方式
=SUM(OFFSET(R[1]C,,,MATCH(10^16,R[1]C:R[99]C,1)))
式を貼り付けたときは、同じようになります。

式入力したいのでしたら、めったにしないことですが、以下のような使い方になります。

下に向かって最終行を探す場合。

Sub TestFormual()
Dim myRow As Long
myRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
　ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R" & myRow & "C)"
End Sub

数式は数式を置くたびに、Calculate が働きますから、そのままですと、非常に重くなってしまいます。VBAは、あくまでも、VBAらしくするなら、以下のようにします。
ただし、裏技としては、Evaluateを使うことがあります。

Sub testSum()
Dim i As Long, j As Integer, sum As Double
With ActiveSheet
　 For j = 1 To .UsedRange.Columns.Count
　　　For i = 1 To .Cells(65536, j).End(xlUp).Row
　　　　 sum = sum + .Cells(i, j).Value
　　　Next i
　　　.Cells(i, j).Value = sum
　　　sum = 0
　 Next j
End With
End Sub

お礼

どうもありがとうございました。お手数をおかけしました。

回答No.3

mshr1962

#2のmshr1962です。
＞sumの開始セルをA1形式で行固定にせず、終わりのセルを相対参照にしたいということです
特定の行から最終行なら下記に設定が載ってます。
http://www.voicechatjapan.com/excelvba/VBArei10.htm

例A列のA1から最終行の和の数式をセットする
Dim myRow As Long
myRow=Range("A1").End(xlDown).Row
ActiveCell.Value = "=SUM(A1:A" & myRow & ")"

回答No.2

mshr1962

R2Cだから固定なのでR[2]Cにすれば固定ではなくなります。

R1C1形式の場合(現在位置がA1として)
絶対参照（列行）　R1C1=$A$1
絶対参照(列)　R[0]C1=$A1
絶対参照(行)　R1C[0]=A$1
相対参照　R[0]C[0]=A1
※上記の[0]は省略可
ただし、"=SUM(R[2]C:R[-1]C)"は循環参照になります。
直上の2つのセルなら、"=SUM(R[-2]C:R[-1]C)"ですね。

回答No.1

Wendy02

こんにちは。

＞行固定をしないで記述する方法はありますか？

R1C1方式とA1方式との違いが分っていないようですね。
一体、どのように式を入れようとしているのでしょうか？

　ActiveCell.FormulaLocal ="= (A1方式の式）"

でも使ってみたらいかがですか？
それから、ある程度、VBAが分ってくると、VBAで式を入力するようなことはしませんね。最初から、範囲が変わる式を入れれば済むことですから。

補足

どうもありがとうございます。
>それから、ある程度、VBAが分ってくると、VBAで式を
>入力するようなことはしませんね。最初から、範囲が
>変わる式を入れれば済むことですから
とはどういう意味でしょうか。
やりたいのは、sumの開始セルをA1形式で行固定にせず、終わりのセルを相対参照にしたいということです。
そういうことは可能なんでしょうか。みなさんの回答を拝見させていただくと、データの個数が変わってしまう場合にはできないように思えますが。