[[20050930092135]] 『VBAデータの集計』(ゆうり) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『VBAデータの集計』(ゆうり)
 いつもお世話になっております。
 上記の件で勉強がてらマクロをくんでみました。
 なんとか動きはしますが、いくつか不明な点がありますので、ご教授いただければ幸いです。

 Sheet1 データベースのシート
  A	  B	 C	     D	E	F
 1 注文日	  届先	 商品名	     備考	注文先	数量
 2 2005/9/30 Aビル	 トイレットペーパー		1
 3 2005/9/30 Aビル	 水石鹸			     4
 4 2005/9/30 Bビル	 トイレットペーパー		4
 5 2005/9/30 Cビル	 ゴミ袋      		1
 6 2005/9/30 Cビル	 トイレットペーパー		2
 7 2005/9/30 Cビル	 水石鹸			     2

 Sheet6 Cビル使用量集計シート
   A     B	   C     D   E ・・・・・・・・  N   O
 8       トイレットペーパー ゴミ袋			水石鹸	
 9      使用量 金額	   使用量 金額			使用量 金額
 10          \2,165	  \2,145		    \2,860
 11 2004年11月
 12 2004年12月
 13 2005年1月
 14	・
 15	・
 16	・
 17 2005年10月
 18 合計													

 Sheet1に各ビルでのトイレットペーパー等の使用量を入力します。
 現在は800行くらいです。
 Sheet6に前年の11月からその年の10月までに使用したものの総数をまとめます。
 B8・C8は結合セルで、以降D8・E8、F8・G8と結合し、商品名を入力してあります。

 以下のマクロです。

 Option Explicit
 Sub total()

 Dim i As Integer, r As Integer
 Dim myrange As Range

 With Worksheets(1)
.Range("A1").AutoFilter field:=2, Criteria1:="Cビル"
Set myrange = .Range("F1", .Range("F1").End(xlDown))
    For i = 2 To 14 Step 2
        .Range("A1").AutoFilter field:=3, Criteria1:=Worksheets(6).Cells(8, i).Value '(a)
            For r = 11 To 21
                .Range("A1").AutoFilter field:=8, Criteria1:=">=" & Worksheets(6).Cells(r, 1).Value, _
                    Operator:=xlAnd, Criteria2:="<" & Worksheets(6).Cells(r + 1, 1).Value '(b)
                Worksheets(6).Cells(r, i).Value = WorksheetFunction.Subtotal(9, myrange)
            Next r
        .Range("A1").AutoFilter field:=8, Criteria1:=">=" & Worksheets(6).Cells(22, 1).Value, _
                Operator:=xlAnd, Criteria2:="<" & DateSerial(Year(Date), 11, 1)
        Worksheets(6).Cells(22, i).Value = WorksheetFunction.Subtotal(9, myrange)
    Next i
.Select
.ShowAllData
.Cells(Range("Sheet1!A65536").End(xlUp).Row + 1, 1).Select '(c)
End With
End Sub

 1)Setステートメントの正しい位置がわからないので(a)・(b)の後にも入れてみましたが、
違いがよくわからず、尚且つどこでも結果はおなじでした。
 こういったマクロの場合はどこに入れるのがよりよいのでしょうか?
 2)Setステートメントの後は、最後に =Nothing で解除したほうがよいとの記述をみかけましたが、
解除していないサンプルもありますし、本当のところはどうなのでしょう?
 3)(c)のコードだけでは「Selectメソッドが失敗しました」とのエラーが出ます。
.Selectを入れて動くようにはなりましたが、納得ができていません。
このコードで失敗するのは何故ですか?違うシートがアクティブだから?
 4)最終的にSheet1のオートフィルタを解除し、グループ化してある箇所をグループにまとめた状態
(アイコン(?)がプラスになっている状態)にしたいのですが、どう記述すればよいのでしょう?
 5)終了まで20〜30秒かかりますが、もっと早くなりますか?

 以上、ヒントだけでもいただけたら・・・。
 宜しくお願いいたします。


 > 1)
 myrange を使う前の行なら、どこでもかまいません。
 コードが読みやすいところなら、どこでも良いと思います。 

 > 2)
 プロシージャ内で宣言した変数は、 End Sub までいくと
 開放されるので、 =Nothing しなくても大丈夫です。
 規模の大きなVBAで、グローバル変数などを利用するとき等や、
 ADO など外部にアクセスするようなものでなければ、
 気にしないでも大丈夫だと思います。

   :

  (INA)


 質問の回答ではありませんが、
  「年月」とかのワーク項目を追加して、
  ピボットテーブルを使用する
 という案はいかがでしょう。(あきお)


 > 1)Setステートメントの正しい位置がわからないので(a)・(b)の後にも入れてみましたが、
 > 違いがよくわからず、尚且つどこでも結果はおなじでした。
 >  こういったマクロの場合はどこに入れるのがよりよいのでしょうか?

 動的に指定する等の必要が無ければFor文より前で行う方がよいです。
 現在書かれている位置で問題ないと思います。

 2)Setステートメントの後は、最後に =Nothing で解除したほうがよいとの記述をみかけましたが、
 解除していないサンプルもありますし、本当のところはどうなのでしょう?

 =Nothing で解除したほうがよいです。
 解除していないサンプルは、サンプルだから解除していないのでしょう。

 Setすることで、システム リソースおよびメモリ リソースに対してオブジェクトを割り当てています。
 Exit した場合等に開放されるはずですが、まれにうまく開放されない場合があるようです。
(特に古いOSでは)
 オブジェクトの使用するリソース量によっては、解除しないと処理している最中に
 メモリリークが発生する可能性があります。

 最近のPCやOSを使っていて、短いソースならまず無いとは思いますが、
 基本的に使い終わったら必ず開放する癖を付けておかれた方が良いと思います。

 > 3)(c)のコードだけでは「Selectメソッドが失敗しました」とのエラーが出ます。
 > .Selectを入れて動くようにはなりましたが、納得ができていません。
 > このコードで失敗するのは何故ですか?違うシートがアクティブだから?

 いいえ、違います。

 .Cells(Range("Sheet1!A65536").End(xlUp).Row + 1, 1)
 まずこの行は何をしたいのでしょうか?

 何かの値を変数に格納しているわけでもなく、Selectしたいわけでもないですよね?
 何も命令がない為のエラーです。

 Selectする必要がないなら、この行が無くても処理は同じになると思います。

 > 4)最終的にSheet1のオートフィルタを解除し、グループ化してある箇所をグループにまとめた状態
(アイコン(?)がプラスになっている状態)にしたいのですが、どう記述すればよいのでしょう?

 自動マクロで試してみてはいかがでしょうか?

 集計を使うんですよね?こんな感じかな?
  ActiveSheet.Outline.ShowLevels RowLevels:=2
  ActiveSheet.Outline.ShowLevels RowLevels:=1

 > 5)終了まで20〜30秒かかりますが、もっと早くなりますか?

 AutoFilter内の引数内で取得している値は、一旦変数に入れたら多少変わるかもしれません。
  Worksheets(6).Cells(r, 1).Value
  Worksheets(6).Cells(r + 1, 1).Value
  Worksheets(6).Cells(r, i).Value
  WorksheetFunction.Subtotal(9, myrange)
 あんまり変わらないとは思いますが・・・状況によっては余計遅くなるかも(;´∀`)

 それから、この結果↓は、
  WorksheetFunction.Subtotal(9, myrange)
 もしForの外で実行しても同じ結果になるのでしたらForの外(Setの次の行くらい)
 で変数に値を入れてから、その変数をForの中で使用してみてください。

 では頑張ってください。(yc)


 INAさん、あきおさん、ycさん、お答えありがとうございます。

 ピボットにつきましては、実はあんまり使い慣れずつい避けて通ってしまって・・・。
せっかくご提案いただきましたので、このコードが完成しましたら、
勉強してみようと思います。

 まず、書き忘れていましたがWindowsXPでExcel2003利用ですし、規模も小さいVBAなので
2)については問題なさそうですね。
1)についても、問題ないようなので解決です。

 で、残りの点ですが、
3)について。
.Cells(Range("Sheet1!A65536").End(xlUp).Row + 1, 1).Select
でSheet1 A列の最終セルを選択して終了したいのです。
ただ、このコードだけだとエラーがでてしまうのです。(TT)
Sheet6がアクティブになっている状況においてのこのコードは失敗となり、
デバックしてSheet1をアクティブにし続行すると動いたのでそういった理由?と思ったわけです。
ですので、.Selectを追加してみました。
説明が乏しくてすみませんでした。
何か回避方法がありますでしょうか?
それともこのコードについての理解の仕方がまちがっているのでしょうか?
4)について。
ycさんの仰るとおり、マクロの記録をやってみます。
このコードも何度も記録しながら作ったところもあるのに
どうしてこれに関しては思い至らなかったのか・・・(^^;
やってみてわからないことがあれば、またお願いします。
5)について。
ycさんご提案の件も試してみます。
ありがとうございます。

 お時間あるときで結構ですので、もう少しだけお付き合いいただけたらとおもいます。
宜しくお願い致します。

 (ゆうり)

 >Sheet6がアクティブになっている状況においてのこのコードは失敗となり、
 >デバックしてSheet1をアクティブにし続行すると動いたのでそういった理由?と思っ たわけです。
 ゆうりさんの言うとおり、アクティブではないシートのセルを Select はできません。(アカギ)

 アカギさん、ありがとうございます。
やはりそうなのですね。
では、.Selectを入れることで解決とします。

 グループ化の件も記録でできましたので、ご報告します。
ycさんの記載されているそのままのコードです。

 皆様のおかげでなんとかしたいことが完成しました!
ありがとうございました!!!
(ゆうり)


 時間が無くて途中までしか書けませんでしたが解決したようですね。(^^;)

 =Nothing に関する過去ログです。ご参考までに。
[[20041124212113]] 
   (INA)

 ふと覗いたらコメントが!!

 INAさん、お忙しい中ありがとうございます!
もう少し待ってたらINAさんのご意見きけたんですね〜惜しいことをしました(--;

 さて、ご紹介いただいた過去ログ、拝見しました。
拝見しましたが・・・挫折です(TT)
皆様の言葉がハイレベルすぎて脳みそが溶解してしまいました(^^;
これが理解できるようになる日はいつになることやら・・・。
少しでも近づけるようにがんばります!!
(ゆうり)

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.