Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
9002 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【29877】行数が決まっていない列の合計を計算するには？ hi-lite 05/10/15(土) 12:30 質問[未読]
【29878】Re:行数が決まっていない列の合計を計算す... かみちゃん 05/10/15(土) 12:42 回答[未読]
【29879】Re:行数が決まっていない列の合計を計算す... hi-lite 05/10/15(土) 13:02 お礼[未読]
【29880】Re:行数が決まっていない列の合計を計算す... hi-lite 05/10/15(土) 13:10 質問[未読]
【29881】Re:行数が決まっていない列の合計を計算す... かみちゃん 05/10/15(土) 13:16 回答[未読]
【29882】Re:行数が決まっていない列の合計を計算す... hi-lite 05/10/15(土) 13:32 質問[未読]
【29884】Re:行数が決まっていない列の合計を計算す... かみちゃん 05/10/15(土) 13:47 回答[未読]
【29886】Re:行数が決まっていない列の合計を計算す... hi-lite 05/10/15(土) 15:38 質問[未読]
【29889】Re:行数が決まっていない列の合計を計算す... かみちゃん 05/10/15(土) 16:01 回答[未読]
【29892】Re:行数が決まっていない列の合計を計算す... hi-lite 05/10/15(土) 16:20 お礼[未読]
【29885】Re:行数が決まっていない列の合計を計算す... Kein 05/10/15(土) 15:23 回答[未読]
【29888】Re:行数が決まっていない列の合計を計算す... hi-lite 05/10/15(土) 15:56 お礼[未読]
【29890】Re:行数が決まっていない列の合計を計算す... かみちゃん 05/10/15(土) 16:06 発言[未読]
【29893】Re:行数が決まっていない列の合計を計算す... hi-lite 05/10/15(土) 16:42 お礼[未読]
【29891】Re:行数が決まっていない列の合計を計算す... Kein 05/10/15(土) 16:19 発言[未読]
【29895】Re:行数が決まっていない列の合計を計算す... hi-lite 05/10/15(土) 17:00 お礼[未読]
【29896】Re:行数が決まっていない列の合計を計算す... Kein 05/10/15(土) 17:51 発言[未読]
【29877】行数が決まっていない列の合計を計算する...
質問  hi-lite  - 05/10/15(土) 12:30 -
引用なし
パスワード
   いつも拝見させて戴いております。

行数が決まっていない列の合計を
計算するにはどうすればよいかご教授下さい。
　
　 Range("B2").Select
　 Selection.End(xlDown).Select　
’ここまでで最終行番号を取得したことは解るようになって参りました。
’その下のセルに合計を計算したいと考えたのですが…
'全く解らなかったので、マクロの記録で最終行番号取得後、Enterを押してみたら…
　 Range("B1469").Select
’台無し…
’その上、計算式もどんな数値を入れて良いのやら…
　 ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-1469]C:R[-1]C)"

過去ログの検索ワードでも構いませんので
何かヒントをお願いいたします。
【29878】Re:行数が決まっていない列の合計を計算...
回答  かみちゃん  - 05/10/15(土) 12:42 -
引用なし
パスワード
   こんにちは。かみちゃん　です。

>行数が決まっていない列の合計を
>計算するにはどうすればよいかご教授下さい。

たとえば、B列の合計を出す場合ですと、
ActiveCell.Formula = "=SUBTOTAL(9,B1:" & Range("B65536").End(xlUp).Address(0, 0) & ")"
でできます。
【29879】Re:行数が決まっていない列の合計を計算...
お礼  hi-lite  - 05/10/15(土) 13:02 -
引用なし
パスワード
   ▼かみちゃん さん：
御返事ありがとうございます。

>たとえば、B列の合計を出す場合ですと、
>ActiveCell.Formula = "=SUBTOTAL(9,B1:" & Range("B65536").End(xlUp).Address(0, 0) & ")"
>でできます。

　　Range("B2").Select
　　Selection.End(xlDown).Select
　　ActiveCell.Formula = "=SUBTOTAL(9,B1:" & Range("B65536").End(xlUp).Address(0, 0) & ")"
ご教授戴いたもので実行してみたのですが、何故か最終行番号に「循環参照」に
なってしまったのですが、なにか余計なことしたのでしょうか？
あっ…B列は計算式が入っているのですが、なにか関係ありますか？
【29880】Re:行数が決まっていない列の合計を計算...
質問  hi-lite  - 05/10/15(土) 13:10 -
引用なし
パスワード
   ▼かみちゃん さん：
題名横の種別を間違えてしまいました。

　　Range("B2").Select
　　Selection.End(xlDown).Select
　　ActiveCell.Formula = "=SUBTOTAL(9,B1:" & Range("B65536").End(xlUp).Address(0, 0) & ")"
ご教授戴いたもので実行してみたのですが、何故か最終行番号に「循環参照」に
なってしまったのですが、なにか余計なことしたのでしょうか？
あっ…B列は計算式が入っているのですが、なにか関係ありますか？
【29881】Re:行数が決まっていない列の合計を計算...
回答  かみちゃん  - 05/10/15(土) 13:16 -
引用なし
パスワード
   こんにちは。かみちゃん　です。

>最終行番号に「循環参照」になってしまったのですが、なにか余計なことしたのでしょうか？
>あっ…B列は計算式が入っているのですが、なにか関係ありますか？

「循環参照」というのは、お互いのセルを計算式の参照範囲に含めているということです。
つまり、SUBTOTAL関数のセル範囲B1〜B列の最終行のうち、そのSUBTOTALの式が入っているセルを参照するような計算式を入れていませんか？

たとえば、
B1に=B3
B3に=SUBTOTAL(9,B1:B3)
とすると循環参照になります。
【29882】Re:行数が決まっていない列の合計を計算...
質問  hi-lite  - 05/10/15(土) 13:32 -
引用なし
パスワード
   ▼かみちゃん さん：

>「循環参照」というのは、お互いのセルを計算式の参照範囲に含めているということです。
>つまり、SUBTOTAL関数のセル範囲B1〜B列の最終行のうち、そのSUBTOTALの式が入っているセルを参照するような計算式を入れていませんか？

申し訳ありません。
質問の際に「そんな重要じゃないかな？」と
思い、Ｂ列と言うことで質問いたしましたが、
実際にはＳ列でマクロによって、
=SUM(RC[-3]-RC[-1])
という計算式が入っております。
本日作成した表の最終行は１４６７行なのですが
ご教授いただいた合計の計算式も１４６７行目に
入っております。
１４６８行目に列の合計計算式を入れたいのです。
この表は毎日最終行が変化いたしますので、以前は
おおざっぱに、「５０００行目に計算式入れてりゃいーだろ」
なんてことしておりました。
印刷する必要性に駆られ、慌ててメールした次第です。
この説明でご理解いただけましたでしょうか？
すみません、うまく説明できているのやら…
【29884】Re:行数が決まっていない列の合計を計算...
回答  かみちゃん  - 05/10/15(土) 13:47 -
引用なし
パスワード
   こんにちは。かみちゃん　です。

>１４６８行目に列の合計計算式を入れたいのです。

つまり、S1からS列の最終行までのSUBTOTALをS列の最終行の次の行に求めたいということでしょうか？
であれば、次のように修正してください。
ActiveCell.Formula = "=SUBTOTAL(9,B1:" & Range("B65536").End(xlUp).Offset(1).Address(0, 0)　& ")"
【29885】Re:行数が決まっていない列の合計を計算...
回答  Kein  - 05/10/15(土) 15:23 -
引用なし
パスワード
   Sub MyTotal()
　 With Range("S65536").End(xlUp)
　　　If Left$(.Formula, 4) = "=SUM" Then
　　　　 .Offset(1).FormulaR1C1 = _
　　　　 "=SUBTOTAL(9,R1C:R" & .Row & "C)"
　　　End If
　 End With
End Sub

で、どうでしょーか ?
【29886】Re:行数が決まっていない列の合計を計算...
質問  hi-lite  - 05/10/15(土) 15:38 -
引用なし
パスワード
   ▼かみちゃん さん：

>ActiveCell.Formula = "=SUBTOTAL(9,S1:" & Range("S65536").End(xlUp).Offset(1).Address(0, 0)　& ")"

すみません、やはり最終行で計算されてしまい、“次の行”に行きません。
Offset(1)となっているのに何故でしょうか？
Address(0, 0)ってなんでしょうか？
…「ＶＢＡハンドブック」（￥２５００）片手に
奮闘している物の、ここらが私の限界で…
本の内容では「セルのアドレスを表示する」でして
今回のケースとの関連性が掴めません…
脳味噌の限界値を超えてます…
【29888】Re:行数が決まっていない列の合計を計算...
お礼  hi-lite  - 05/10/15(土) 15:56 -
引用なし
パスワード
   ▼Kein さん：
>Sub MyTotal()
>　 With Range("S65536").End(xlUp)
>　　　If Left$(.Formula, 4) = "=SUM" Then
>　　　　 .Offset(1).FormulaR1C1 = _
>　　　　 "=SUBTOTAL(9,R1C:R" & .Row & "C)"
>　　　End If
>　 End With
>End Sub
>
>で、どうでしょーか ?

！！！！！！！！！！！！！！！！
どうも何も…コピーさせていただいただけで
実行できました。
…意味が解らなくて、窒息死しそうですが…
Kein様、本当にありがとうございます！！！！！！！
これで作業が進みます。助かりました。

…申し訳ないんですが、“凄く暇”なら…で結構なんですが
後学のために
>　　　　　Left$(.Formula, 4) = "=SUM" Then
>　　　　 .Offset(1).FormulaR1C1 = _
>　　　　 "=SUBTOTAL(9,R1C:R" & .Row & "C)"

とはどの様な状況なのか教えていただけないでしょうか？
本当に“凄く暇”ならで結構ですので…
【29889】Re:行数が決まっていない列の合計を計算...
回答  かみちゃん  - 05/10/15(土) 16:01 -
引用なし
パスワード
   こんにちは。かみちゃん　です。

>すみません、やはり最終行で計算されてしまい、“次の行”に行きません。

私が、大きな間違いをしていました。

ActiveCell.Formula = "=SUBTOTAL(9,B1:" & Range("B65536").End(xlUp).Offset(1).Address(0, 0)　& ")"

の式は明らかに間違いです！
ActiveCell〜を考慮することを忘れていました。

　'S列の１行目から最終行までのSUBTOTALを最終行の次の行に求める
　With Range("S65536").End(xlUp)
　　.Offset(1).Formula = "=SUBTOTAL(9,S1:S" & .Row & ")"
　　.Offset(1).Select
　End With
としてください。

>Address(0, 0)ってなんでしょうか？

ヘルプを調べてください。
MsgBox ActiveCell.Address & vbCrLf & ActiveCell.Address(0, 0)
で違いを確認してください。
なお、今回は、外してあります。
【29890】Re:行数が決まっていない列の合計を計算...
発言  かみちゃん  - 05/10/15(土) 16:06 -
引用なし
パスワード
   こんにちは。かみちゃん　です。

>>　　　　　Left$(.Formula, 4) = "=SUM" Then
>>　　　　 .Offset(1).FormulaR1C1 = _
>>　　　　 "=SUBTOTAL(9,R1C:R" & .Row & "C)"
>
>とはどの様な状況なのか教えていただけないでしょうか？
>本当に“凄く暇”ならで結構ですので…

最終行の計算式が=SUMで始まっていれば、１行下に移動して、SUBTOTAL関数を設定するということです。

Left関数
Formulaプロパティ
Offsetプロパティ
FormulaR1C1 プロパティ
をヘルプで調べてみてください。
百聞は一見にしかずです。

あと、「マクロの記録」で記録してみることです。
【29891】Re:行数が決まっていない列の合計を計算...
発言  Kein  - 05/10/15(土) 16:19 -
引用なし
パスワード
   "凄く"というわけでも無いけど、"適当に"ヒマなんで(笑)

If Left$(.Formula, 4) = "=SUM" Then

は、S列最終入力行の数式が「=SUM」で始まっているなら、未だ列全体の合計を
出して無い、とみなしてその１つ下のセルに「=SUBTOTAL」で始まる数式を
入力せよ、というコードです。その際、SUMの数式はR1C1で入力されているようなので、
SUBTOTALも参照形式を合わせて R1C1 にしています。
もちろん最終入力行に SUBTOTAL が入っていれば、何もしないで終了することに
なりますが、それはそちらの =SUM数式を入力するためのマクロ次第、ということ
です。例えばそのマクロが

Range("Q1", Range("Q65536").End(xlUp)).Offset(, 2) _
.FormulaR1C1 = "=SUM(RC[-3]-RC[-1])"

などとしていれば、もし現在のS列の最終入力行を超えて =SUM の式が入力された
場合、SUBTOTALの式は自動的に消えます。そして新たな列の最終入力行の数式は
=SUM で始まることになるので、次回からは

If Left$(.Formula, 4) = "=SUM" Then

の条件に合致するわけです。
お分かりいただけましたか ?
【29892】Re:行数が決まっていない列の合計を計算...
お礼  hi-lite  - 05/10/15(土) 16:20 -
引用なし
パスワード
   ▼かみちゃん さん：
>　'S列の１行目から最終行までのSUBTOTALを最終行の次の行に求める
>　With Range("S65536").End(xlUp)
>　　.Offset(1).Formula = "=SUBTOTAL(9,S1:S" & .Row & ")"
>　　.Offset(1).Select
>　End With
>としてください。

ありがとうございます！！！！！！
kein様のコードと似ているようで、違うんですね？
ともかく、本当にありがとうございます！！
さっそく何がどう作用するのか、違いはどこから来るのか
勉強させていただきます！！！！


>>Address(0, 0)ってなんでしょうか？
>
>ヘルプを調べてください。
>MsgBox ActiveCell.Address & vbCrLf & ActiveCell.Address(0, 0)
>で違いを確認してください。
>なお、今回は、外してあります。

…取り外し可能？？？？？
…ヘルプのヘルプ機能が欲しいぐらいですが、
頑張ってみます！

これからも質問させていただきますので
宜しくお願い申し上げます。
【29893】Re:行数が決まっていない列の合計を計算...
お礼  hi-lite  - 05/10/15(土) 16:42 -
引用なし
パスワード
   ▼かみちゃん さん：

ありがとうございます！

>百聞は一見にしかずです。

ヘルプの文章は、視覚には届くのですが
視覚から脳味噌に直結が上手くできてなくて…

>あと、「マクロの記録」で記録してみることです。

もっぱら、『ＥＸＣＥＬ表の加工を「マクロの記録」で
行い、限界が来たら「ＶＢＡ」を使…教えていただく』
というスタンスです。（他力本願からの脱却を図っております）

って…こんなの「マクロの記録」で出来るんですか？
でも「マクロの記録」の省略に四苦八苦していますので
あんまり複雑なのは当分先かと…

…でも頑張ります！
いつか“最近hi-liteまともな質問してくる様になったなぁ”
と思ってもらえるように（それでも質問）頑張ります。
【29895】Re:行数が決まっていない列の合計を計算...
お礼  hi-lite  - 05/10/15(土) 17:00 -
引用なし
パスワード
   ▼Kein さん：

…すいません…
理解するまであと２〜３日戴けますか？

>If Left$(.Formula, 4) = "=SUM" Then
>
>は、S列最終入力行の数式が「=SUM」で始まっているなら、未だ列全体の合計を
>出して無い、とみなしてその１つ下のセルに「=SUBTOTAL」で始まる数式を
>入力せよ、というコードです。その際、SUMの数式はR1C1で入力されているようなので、
>SUBTOTALも参照形式を合わせて R1C1 にしています。
>もちろん最終入力行に SUBTOTAL が入っていれば、何もしないで終了することに
>なります

ここまでは、息苦しくてもなんとか…

>が、それはそちらの =SUM数式を入力するためのマクロ次第、ということ
>です。例えばそのマクロが
>
>Range("Q1", Range("Q65536").End(xlUp)).Offset(, 2) _
>.FormulaR1C1 = "=SUM(RC[-3]-RC[-1])"
>
>などとしていれば、もし現在のS列の最終入力行を超えて =SUM の式が入力された
>場合、SUBTOTALの式は自動的に消えます。そして新たな列の最終入力行の数式は
>=SUM で始まることになるので、次回からは
>
>If Left$(.Formula, 4) = "=SUM" Then
>
>の条件に合致するわけです。

…自動的に消える？？？？？？
…新たな列の最終入力行の数式は=SUM で始まることになる？？？？？？
次回からの条件は以前と変わったんでしょうか？？？？？？？？
既に脳味噌に酸素の供給はされておりません…

…頑張ります…取り敢えず…復習を続けるように致します。
ありがとうございました……
【29896】Re:行数が決まっていない列の合計を計算...
発言  Kein  - 05/10/15(土) 17:51 -
引用なし
パスワード
   んーと・・何と説明してよいか・・。
例えば現在、S列の最終入力行が 10 であったとしてます。
このとき、S10 には既に =SUBTOTAL(R1C:R9C) という列の合計をする式が
入っているとします。つまり S1:S9 の範囲は =SUM で始まる数式ですね。
当然、その数式が参照している範囲は、Q1:Q9 と R1:R9 になっているはずです。
そしてあなたが書いたマクロ↓を実行したとき、仮に Q11 まで値が入力されて
いたとすると

Sub MySUM()
　 Range("Q1", Range("Q65536").End(xlUp)).Offset(, 2) _
　 .FormulaR1C1 = "=SUM(RC[-3]-RC[-1])"
End Sub

によって、S1:S11 の範囲に =SUM で始まる式が入りますよね ?
そーしたら当然、先にあった S10 の SUBTOTAL の式は上書きされて消えてしまい、
S列の最終入力行は 11 になり、そこには =SUM で始まる式があるのだから
私の提示した Sub MyTotal() を実行すると

IF Left$(.Formula, 4) = "=SUM" Then

の判定は"真"となって、新たな SUBTOTAL の数式を入力できるわけです。
念の為付け加えておくと、もし Q列の最終入力行が 8 に減ってしまったときは、
あなたのマクロを実行した後も、当然 S列には変化がなく、従って Sub MyTotal()
を実行しても何も起きないのです。「常にデータが増えていく」ということを
前提にしているためです。万が一、データが減ることが予想されるなら、あなたの
マクロは

Sub MySUM()
　 Columns(19).ClearContents
　 Range("Q1", Range("Q65536").End(xlUp)).Offset(, 2) _
　 .FormulaR1C1 = "=SUM(RC[-3]-RC[-1])"
End Sub

というように、"いったんS列をクリアしてから" 数式を入力するように、変更しない
といけません。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
9002 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free