エクセルのマクロで選択範囲の合計を求めたい

質問

A2:A10に数字があってその一つ下に合計をVBAで出したいのですがどうしたらよいでしょうか。
数字はA2:A50とかA2:A30とか毎回変わります、そしてその下に合計を出します。
マクロで記録したらその選択範囲まで記録されてしまうので
違う範囲が求められません。
とにかくある範囲のセルを選択してその選択範囲の一番下か、または選択範囲の一つ下に合計を出したいのです。
もしくはそのアクティブセル上にある数字の合計です。

どうぞご教示下さい。

回答No.8

kimino_naha

No1の追伸です。

計算するシートがアクティブであれば、セルはどこがアクティブになっていてもマクロの実行には影響ありません。たとえ AZ10000 がアクティブでも。

計算するシートをアクティブにしたくない場合は、3行目で計算するシートを指定します。
sheet1 の場合は
with worksheets("sheet1")
となります。この場合シート2でマクロを実行してもシート1の合計ができます。

回答No.7

kimino_naha

こんばんわ、No1です。

ごめんなさい、3行目の綴りを誤っていました。with activesheets ではなく with activesheet です。失礼しました。

sub TestSum()
dim I as long, R as long
with activesheet
for I = 2 to .cells(10000,1).end(xlup).row
R = R + .cells(I,1)
next I
.cells(I+1,1)=R
end with
end sub

.cells(行,列) であるといいましたが、列についても for to ... next 文を平行して使用すれば、10行でも100行でも同じマクロで合計を求めることができます。

下から3行目の .cells(I+1,1) で1つ飛ばし .cells(I+2,1) で2つ飛ばしになります。

回答No.6

Wendy02

こんばんは。

>数式をコピーしたいので$を外して相対参照にしたいのですが

それは簡単なことです。以下のようにすると相対参照になります。
　.Address(0, 0)

Sub Test2R()
'範囲選択
Const N As Long = 2　'何個下？
Const L As Long = 0 '何個左へ
　With Selection
　 .Cells(.Count).Offset(N, L).FormulaLocal = "=SUM(" & .Address(0, 0) & ")"
　 .Value = .Value '定数化
　End With
End Sub

>A5からとか範囲を変えるにはどうしたらいいでしょうか。
こちらの場合は、範囲の起点の部分は、絶対参照になります。

Sub Test1R()
Const M As Long = 5　'最初は何行目？
Const N As Long = 2　'何個下？
Const L As Long = 1　'何個左へ
　With ActiveSheet
　　　With .Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(N, L)
　　　　　 .FormulaLocal = "=SUM(R" & M & "C[-" & L & "]:R[-" & N & "]C[-" & L & "])"
　　　　　 '.Value = .Value '定数化
　　　End With
　End With
End Sub

回答No.5

imogasi

質問表現が十分でない。
（A)A1:A10とか言うのを（範囲を）どのようにしてプログラムに伝えるのですか。
（１）マウスで範囲指定
（２）InputBoxで範囲を問い合わせて、答えによる。INPUTBOXのTYPE:=8
（３）文字列で答える
などどういう風に考えていますか
（B)計算に入るキッカケはどう考えていますか
イベントという言葉を知っていますか。
（１）ボタンを押すーワンステップ手間がかかる
（２）マウスで範囲指定の都度
（３）（A)の（２）、（３）ならEnteryやOKの直後でしょうが。
こういう意識が無いようで、取り組むテーマとしては、ちょっと早すぎるのでは。
（A)(1)と（B)(2)のケースで表示だけなら、
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
a = Application.WorksheetFunction.Sum(Target)
MsgBox a
End Sub
のように簡単になるが、いつもこのようになると、しつこいことを
認識し、どういうのが良いか考えてから、ではコードはどうする、ということを質問すべきです。
（A)の（２）は
Sub test01()
Dim a As Range
Set a = Application.InputBox("範囲", Type:=8)
s = Application.WorksheetFunction.Sum(a)
MsgBox s
End Sub
モジュールの実行のような面倒なステップを経てよいなら
Sub test01()
Dim a As Range
Set a = Application.InputBox("範囲", Type:=8)
s = Application.WorksheetFunction.Sum(a)
MsgBox s
c = a.Cells(1).Column
r = a.Cells(1).Row + a.Rows.Count
Cells(r, c) = s
End Sub
など。

回答No.4

n-jun

ANo.2です。
>一つ下でなく二つ下に合計を出すにはどうしたらいいでしょうか。

Sub　test2()
　　　　Dim　r　As　Range
　　　　Set　r　=　Selection
r.Offset(r.Rows.Count　+　1).Resize(1).Value　=　_
　　"計　"　&　WorksheetFunction.Sum(r)
End　Sub
こちらではどうでしょうか？

補足

おっとと少し書き方間違えたようです。
計は同じセルでなく左のセルに入れたいです。
例としてB1:B10の合計を計算して
B10の二つ下のB12に合計を出してその隣であるA12に計を
表示するといったものです。
度々すみませんです。

回答No.3

Wendy02

こんばんは。

質問の内容にはブレがあるようです。

>A2:A10に数字があってその一つ下に合計をVBAで出したい
>A2:A50とかA2:A30とか毎回変わります、そしてその下に合計を出します。

'[標準モジュール]が良いです。
アクティブセルの列の範囲の一番下の行の次の下のセルに出す。
数式でない場合は、.Value の　コメントブロックを外す。

Sub Test1()
　With ActiveSheet
　　　With .Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(1)
　　　　　 .FormulaLocal = "=SUM(R2C:R[-1]C)"
　　　　　 '.Value = .Value '定数化
　　　End With
　End With
End Sub

ワークシート関数を応用すればよいです。どこでも、同じ式です。

>ある範囲のセルを選択してその選択範囲の一番下か、または選択範囲の一つ下に合計を出したいのです。

Sub Test2()
'範囲選択
　With Selection
　 .Cells(.Count).Offset(1).FormulaLocal = "=SUM(" & .Address & ")"
　 ' .Value = .Value '定数化
　End With
End Sub

補足

ありがとうございます、出来たようです。
ついでにお願いなのですが
選択範囲の次の下といいましたが二つ下には出来ないでしょうか。
　10　←このように空白を挟んで合計を出したいです。
　10
　　　　　またSub Test1()では常にA2から範囲をとるようですが
計20　　A5からとか範囲を変えるにはどうしたらいいでしょうか。

次にSub Test2()では選択を余分にとって計算したら空白を
挟んで合計を出せますが
数式が=SUM($A$4:$A$11)のように絶対参照になるようです
数式をコピーしたいので$を外して相対参照にしたいのですが
どうすればいいでしょうか。

回答No.2

n-jun

>または選択範囲の一つ下に合計を出したいのです。
のサンプル。

Sub　test()
　　Dim　r　As　Range
　　Set　r　=　Selection
r.Offset(r.Rows.Count).Resize(1).Value　=　_
　　　　WorksheetFunction.Sum(r)
End　Sub

ご参考になれば。

補足

やってみたところ出来ました。
お手数かけるのですが、一つ下でなく二つ下に合計を出すにはどうしたらいいでしょうか。
　5
　5

計10

上記のように表から空白を挟んで合計を出したいのです。

回答No.1

kimino_naha

sub TestSum()
dim I as long, R as long
with activesheets
for I = 2 to .cells(10000,1).end(xlup).row
R = R + .cells(I,1)
next I
.cells(I+1,1)=R
end with
end sub

A列の10000行目から上に向けてデータのある最終行を探しています。
次に2行目から最終行まで変数Rに繰り返し値を代入させています。

cells(行,列)ですのでA列以外であれば列の数字を変更してください。

補足

回答ありがとうございます。
A2:A10に数字を入れてA11を選択状態にしてマクロを実行したら
「オブジェクトが必要です」というエラーが出ます。
デバッグを開いたら
For I = 2 To .Cells(10000, 1).End(xlUp).Row
上の行で止まっているみたいです。
どうすれば合計が出るでしょうか。