[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『同じレイアウトのシートが複数で一つのブックになっていて、そのブックが複数ある場合の集計の仕方について』（ご面倒かけ太郎）
１事業所ごとに、毎月エクセルの月別シートに事業結果を入力して貰っています。
エクセルブックには４月から翌年３月まで12か月分のシートがあり、各月は２シート（４月A・４月B・５月A・５月B…）ずつあります。
また12月Bシートの次に年度集計シートがあり、この年度集計シートも（A・B）２シートあります。
結果的に１事業所当たり、26シートで１ブック（ファイル）となっています。
事業所は18事業所あり、結果的に18個のブック（ファイル）があります。
各シートのレイアウトは、集計シートも含めすべて同じです。
【例】
A B C D E F G H I J K L M N O ・・・
１ あ い う え お 小計 か き く け こ 小計 合計
２ イ
３ ロ 9 8 7 24 １ ２ ５ ８ 32
４ ハ １ ３ ４ ５ 13 ６ ３ １ 10 23
５ ニ
６ ホ
７ へ
８ ト
９
・
・
・
20合計 10 8 3 11 5 37 1 8 3 5 1 18 55
…の様な集計表で、例の「G列」と「M列」には、それぞれ「B〜F」「H〜L」の小計を求めるSUM関数が入っています。また合計の「N列」と「20行」には、それぞれ「G＋M」と「B２〜B19」を合計する関数を入れてあります。
各事業所で結果を入力して貰うのは、【例】のシートで言うと「B2」〜「F19」までと「H２」〜「L19」までです。
これが毎月分（各月２事業あるため２シートずつ）、４月〜12月まであります。
「12月B」シートの後の「集計用シート」（これも「A」・「B」の２シートあります）は、事業所にデータを入れて貰うセルごとに「＝SUM（４月AのB2：12月BのB2）の様に串刺し集計用に関数が入れてあり、これ自体は毎月各事業所が結果を入力すると自動計算されるようになっています。
この18事業所の毎月の事業結果報告を、別のエクセルブックにまとめています。
このブックも、各事業所用のブックと同じで、「４月A」「４月B」…「10月A」「10月B」・・・と言う様に、月別に２シートあり、最後に集計用シート「A」「B」の２シートを付け合計で26シート構成になっています。
この各月のシート（「A」「B」２シートありますが）に、各事業所の各月の集計結果を表示させたいと思っています。この全事業所の集計用ブックの集計シートには串刺し計算したSUM関数が入っており、全事業所の入力したところまでの結果が見られる様になっています。
ちなみに、集計用のブックも含め、各シートはペーパーによる報告用に印刷するためにレイアウトされているので、単に集計結果だけが表示されれば良いだろうと言う事ではないのです。
もしかしたら、とても合理的ではなく面倒な業務実態なのですが、そこのところは業務改善とか言う様な事ではなくご理解いただければと思います。
前提がとても長くなってしまいましたが、ここでお願いです。
この全事業所の集計用ブックの各月シート（「A」「B」２シートありますが）の「B2」〜「F19」、「H２」〜「L19」に、全事業所の集計結果を表示させるには、どの様なマクロ…VBAを書けば良いのでしょうか？
また、そのマクロ…VBAを書いた場合、どの様にボタンを作成すると、ボタンクリック一回で集計した数字が表示される様になるでしょうか？
エクセルの関数は多少使えるのですが、今回のお願いの様に、まったく同じレイアウトのエクセルシートが複数で構成されている複数のブックの数字を、また別の集計用のブックに、毎月結果が集まるごとにボタンクリック一つで集計させるには、どの様にすれば良いのか…正直、似た様な質問や沢山の方のマクロ回答などを見ても良く判らなかったので、お願いをアップさせていただきました。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
言葉が色々あって分かりにくいのですが 各事業所用のブックが18個あって、シート数が （毎月＋集計）×２シート あるわけですね？ 集計用ブックは一つあって、18ある各事業所用ブックの、毎月ごとの集計を、 集計用ブックの同じシートのB2:F19、H2:L19各セルに入れればいいわけですね？
この例、本当にセルの配置とか正しいですか？ それともただの例で、範囲は自分で変更できる力量があるのですか？ もしよければ [[20110209184943]] 『[談]シートレイアウトの投稿どうしてますか？』（ｍｏｍｏ） こちらを使って、正しいレイアウト提示頂けると大変助かります。
もうひとつ、そのブックは、ばらばらのフォルダに入っていますか？ それとも、一つのフォルダにまとまっていますか？ また、集計するブックのみ入っているのか 他のファイルも入っているなら、 ブック名に特定の文字（例えば2015年○○事業所売上月報.xlsmの「月報」が必ずある等）はありますか？
ご確認お願いします。 （稲葉） 2016/03/07(月) 11:10
シートのレイアウトについてですが…
すべてのシートは全く同じレイアウトです。と言うより、１枚のシートを増やして行き名前を変え一つのブックにしてあります。また、ブックをコピーし18事業所と集計用のブックにしています。
レイアウトそのものについては、実際のシートは【例】より大きな…列・行とも項目が多いシートになっています。
いずれにせよ、列と行の項目に囲まれた範囲のセルに事業結果を数字で入力して貰う事になっています。
ただ、結果がない場合、数字のゼロを含め何も入っていないセルも沢山あります。
シート名は「４月事業１」・「４月事業２」・「５月事業１」・・・と言う様に付けてあり、各ブックとも共通です。
またブック名は「01【事業所１】28（事業A）」と言った様につけてあり、【】でくくられた事業所名と頭の数字のみ異なります。頭の数字は「00（集計用ブック）」から始まり「01」「02」と振ってあります。
まだ言葉足らずのところはあると思いますが、よろしくお願いいたします。
（ご面倒かけ太郎） 2016/03/07(月) 12:02
＞すべてのシートは全く同じレイアウトです。 分かってます。
もう一度聞きますね。 番号毎に答えていただければと思います。
１）B2:F19、H2:L19の範囲は固定で良いですか？それとも、本番は全く違うセルですか？ ２）一つのフォルダーに全てのブックが入っていますか？ ３）一つのフォルダーに入っている場合、集計対象以外のブックは入っていますか？ ４）３）が「はい」の場合、集計対象のブックは、頭文字が01〜18のみ集計でよろしいですか？
（稲葉） 2016/03/07(月) 12:31
質問理解してなかったようで済みません。
１）・・・B2:F19、H2:L19は例えで、実際には「D12:E13」〜「Z98:AA99」の範囲と「AL12:AM13」〜「BH88:BI99」の二つの範囲です。
「D12:E13」となっているのは、結果を入れて貰うセルを何故か４つのセルで結合してあります。
この辺は何故そうしたのか？…私にも良く判りません。ただ、項目の作成状況からすると項目はセルの結合があった方が項目名の見た目の感じから良い様ですが、結果を入れるセルは結合されたセルでなくとも良さそうです。
２）一つのフォルダーにすべてのブック（18事業所用ブック＋集計用のブック）が入っています。
３）２）以外のその他のブックは入っていません。
４）集計対象のブックはブック名の頭が「01」〜「18」のみの集計となります。
ご質問の回答のみにしました。
引き続きよろしくお願いいたします。
（ご面倒かけ太郎） 2016/03/07(月) 13:22
必ずコピーを取ってから実行してください。
１）集計用のブックに標準モジュールを挿入し、そこに以下のコードを入れてください。 ２）コメントがあるところを自分の環境に合わせて変更してください。 ３）実行の仕方とかは調べればいくらでも出てくると思うので割愛します。
フォルダの中は、このようになっていると想定しています。 00【集計表】28（事業A).xlsm 01【事業所A】28（事業A).xlsm 02【事業所B】28（事業A).xlsm 03【事業所C】28（事業A).xlsm
あんまりうまいやり方ではありませんが、結合セルがあって全て同じ形というのを逆手にとって エクセルの標準機能を利用した方法です。
Option Explicit
'=====実行するプロシジャ===========================
Sub 集計開始()
'==================================================
Dim wb As Variant
Dim ws As Worksheet
Dim myWB As Workbook
Dim HANI(1) As String
'集計範囲を文字列で設定
HANI(0) = "B2:F19"
HANI(1) = "H2:L19"
If MsgBox("集計を実行する前に、集計ブックに入力されたデータを一度リセットします。よろしいですか？", vbYesNo) = vbYes Then
With ThisWorkbook
For Each ws In .Sheets
ws.Range(HANI(0) & "," & HANI(1)).ClearContents
Next ws
For Each wb In OpWB
For Each ws In wb.Sheets
'形式を選択して貼付で、値・加算として張り付ける
ws.Range(HANI(0)).Copy
.Sheets(ws.Name).Range(HANI(0)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
ws.Range(HANI(1)).Copy
.Sheets(ws.Name).Range(HANI(1)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
Next ws
wb.Close
Next wb
End With
MsgBox "集計が完了しました"
End If
End Sub
'=====ブックのオブジェクト配列を作成する===========
Private Function OpWB() As Variant
'==================================================
Dim myDir As String
Dim F As String
Dim Nm As String
'集計するブック名の頭文字をカンマで区切る
Nm = "01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18"
'集計対象ブックが保管されているフォルダのパス
myDir = "C:\エクセルの学校\面倒\"
With CreateObject("Scripting.Dictionary")
'対象フォルダの.xlsmブックを調べて、頭文字が設定値ならばブックのオブジェクトを
'Dictionaryのオブジェクトに入れる
F = Dir(myDir & "*.xlsm")
Do While Len(F) > 0
If Nm Like "*" & Left(F, 2) & "*" Then
Set .Item(myDir & F) = Workbooks.Open(myDir & F, ReadOnly:=True)
End If
F = Dir()
Loop
OpWB = .items
End With
End Function
（稲葉） 2016/03/07(月) 15:05
今回ご案内いただいたものをご案内と自助努力により何とか実行出来る様にしてみたいと思います。
ただご案内いただいた中で１点お伺いしたいのですが、それは…
『
フォルダの中は、このようになっていると想定しています。
00【集計表】28（事業A).xlsm 01【事業所A】28（事業A).xlsm 02【事業所B】28（事業A).xlsm 03【事業所C】28（事業A).xlsm 』 との事ですが、小生のファイルの拡張子は皆「.xls」ですが、これはこれで良いのでしょうか？
きわめて基本的、初歩的質問じゃないか…とも思いますがどうなんでしょうか？
（ご面倒かけ太郎） 2016/03/07(月) 15:38
動かしてないですが ＞F = Dir(myDir & "*.xlsm") この部分を、 ＞F = Dir(myDir & "*.xls") このように変えれば問題なく動く想定です。
（稲葉） 2016/03/07(月) 16:36
ともかくとても感謝しております。
今後とも宜しくお願いいたします。
（ご面倒かけ太郎） 2016/03/07(月) 22:31
さっそく作成いただいたものを試してみました。
試すに当たり、
HANI(0) = "B2:F19"
HANI(1) = "H2:L19"
の値を、HANI(0) = "D12:Z98"
HANI(1) = "AL12:BH88"
…と、それぞれ結合されているセルの左上のセルを指定したのですが、実行時エラー「1004：結合されたセルの一部を変更することはできません。と警告が出ました。
デバッグを選んだところ「ws.Range(HANI(0) & "," & HANI(1)).ClearContents」が黄色く表示されていました。
自分なりに調べ、「.MergeArea」を付け加えてみましたが、２か所につけると「コンパイルエラー：修飾子が不正です」となり１か所につけると「実行時エラー：アプリケーション定義またはオブジェクト定義のエラーです」と出ます。
再度調べると「.MergeArea」は結合された【複数の範囲】を指定する場合には使えない。らしいのですが、結合された複数のエリアを指定したい場合はどの様にするべきなのでしょうか？
再度ご教示をお願いいたします。
（ご面倒かけ太郎） 2016/03/08(火) 10:23
これも使うメソッド名と言うかメソッドが悪いからなのでしょうが、４つのセルで結合されている一つのセルを連続して複数指定した上で、さらに同様の結合された複数セルの範囲を指定する事に間違いがあるのでしょうか？
いろいろ結合されたセルの扱いについて調べてはいるのですが、まだ良く判りません。状態です。
（ご面倒かけ太郎） 2016/03/08(火) 11:00
結合されたセルを全て含んだセル範囲であれば、ClearContentsで行けると思いますが。 ところで、D12:E13が結合され、それがZまで並んでいるのであれば、AA99が最後のセルになりますよね？ ＞の値を、HANI(0) = "D12:Z98" ＞ HANI(1) = "AL12:BH88"
なんで最初の説明と違う範囲なんですか？ （稲葉） 2016/03/08(火) 12:34
HANI(0) = "D12:Z98"の記述については、結合したセルのため最後の結合したセルの左上のセル番号？を書きました。セルの結合とか関係なしに範囲の最後はと言う事になると、ご指摘のとおりAA99が最後となります。
HANI(1) = "AL12:BH88"についても同様で、結合とか関係なく範囲の最後と言う事になるとBI89となります。
早速、"AA99"と"BI89"と書き直し試してみます。
ありがとうございます。
（ご面倒かけ太郎） 2016/03/08(火) 13:34
ちなみに、今回のテストでは集計用ブックの最初のシートの表の欄外に「ボタン」を作り、そのボタンのマクロとして今回いただいたマクロを登録する様にしたのですが…。
また、いただいたコメントに基づき小生のテスト環境に合わせた（つもり？）なのですが…。
以下に、作成いただいたVBAの自分用の修正を加えたものを貼り付けます。何か間違っていたらご指摘いただければと思います。
Option Explicit
'=====実行するプロシジャ===========================
Sub 集計開始()
'==================================================
Dim wb As Variant
Dim ws As Worksheet
Dim myWB As Workbook
Dim HANI(1) As String
'集計範囲を文字列で設定
HANI(0) = "D12:AA99"
HANI(1) = "AL12:BI89"
If MsgBox("集計を実行する前に、集計ブックに入力されたデータを一度リセットします。よろしいですか？", vbYesNo) = vbYes Then
With ThisWorkbook
For Each ws In .Sheets
ws.Range(HANI(0) & "," & HANI(1)).ClearContents
Next ws
For Each wb In OpWB
For Each ws In wb.Sheets
'形式を選択して貼付で、値・加算として張り付ける
ws.Range(HANI(0)).Copy
.Sheets(ws.Name).Range(HANI(0)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
ws.Range(HANI(1)).Copy
.Sheets(ws.Name).Range(HANI(1)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
Next ws
wb.Close
Next wb
End With
MsgBox "集計が完了しました"
End If
End Sub
'=====ブックのオブジェクト配列を作成する===========
Private Function OpWB() As Variant
'==================================================
Dim myDir As String
Dim F As String
Dim Nm As String
'集計するブック名の頭文字をカンマで区切る
Nm = "01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18"
'集計対象ブックが保管されているフォルダのパス
myDir = "C:\Users\増田\Desktop\test 求職・区別"
With CreateObject("Scripting.Dictionary")
'対象フォルダの.xlsmブックを調べて、頭文字が設定値ならばブックのオブジェクトを
'Dictionaryのオブジェクトに入れる
F = Dir(myDir & "*.xls")
Do While Len(F) > 0
If Nm Like "*" & Left(F, 2) & "*" Then
Set .Item(myDir & F) = Workbooks.Open(myDir & F, ReadOnly:=True)
End If
F = Dir()
Loop
OpWB = .items
End With
End Function
（ご面倒かけ太郎） 2016/03/08(火) 13:54
＞ただ、集計用ブックの各月のシートには集計結果が反映されていない様なのですが、 最後までエラーが無く走ったのなら、そんなことは無いのですが・・・ 下記のコードの途中に
For Each ws In wb.Sheets 〜 Next ws とあると思いますが、どこでも良いので Debug.Print ws.Name を1行挿入してください。
VBEの画面の「表示」から「イミディエイトウィンドウ」を表示させて、集計したいシート名があるか確認してください。 （稲葉） 2016/03/08(火) 14:29
「 Next ws」の直前にご案内の１知行を入れ込みました。
ボタンを押し実行すると、ほんとにあっと言う間に「集計が完了しました」となりますが、やはり集計用ブックの各月ごとのシートに18事業所の合計は表示されないままです。
次々とコメント…質問？を発しますが、
集計用ブックの各月の集計用シートに、18ブックの合計が表示されないのは、各事業所のブックに集計用のシートがあるせいでしょうか？
４月A・４月B・５月A…と言ったシートだけにしておかないとまずいのでしょうか？
今回テストした環境は１事業所のブックの各月の表の中の同じ位置に数字を入れ、その入力して貰う部分をコピーし各月にペースト。そして出来上がったブックをコピーし18個にしてみたものです。
いずれのブックにも最初の説明であげた集計用シートが入っています。…なんかこのシートが入っている事で、各月のシートに18ブックの同じ月の集計結果が表示されない事になっているのでしょうか？
（ご面倒かけ太郎） 2016/03/08(火) 14:47
意味ディエイトウインドウには、シート名などは全く表示されていません。
空白です。
（ご面倒かけ太郎） 2016/03/08(火) 14:50
＞意味ディエイトウインドウには、シート名などは全く表示されていません。
ということは、ブックが正常に取得出来ていないということですね。
myDir = "C:\エクセルの学校\面倒\"
myDir = "C:\Users\増田\Desktop\test 求職・区別"
これ見比べてみて、何か足りないところはありませんか？
（稲葉） 2016/03/08(火) 16:53
ご指摘いただき気がつきました。
最後のフォルダ名の後に「￥」マークが抜けていました。
･･･言葉もありません。
もう一度やってみます。
毎度、ほんとうに有り難うございます。
これに懲りず、引き続きよろしくお願いいたします。
（ご面倒かけ太郎） 2016/03/08(火) 18:49
作成していただいたVBAは、いくつかのご指摘を修正し、本日になり実行しました。
結果は、見事に実行され、見事に集計が出来ました。
…本当にありがとうございました。感謝！！感謝！！です。大助かりでした。ありがとうございます。
今回の質問は、やはりこのVBAプログラムについてです。
実行したところ前述のとおりとてもうまく実行出来たのですが、集計結果を見ると「１」足りません。原因を探したところ、またしても私のミスで、18番目のファイル名の頭の数字が「18」ではなく「71」になっていました。
それで、そのファイルの数字は合計されなかったのだな…と思い、ファイルの頭の数字を「71」から「18」に変更しました。
それで再度実行したところ、今度は【実行時エラー'9'：インデックスが有効範囲にありません】となり途中で止まってしまいました。
途中で止まったのは、一番上に開かれたファイルがよりによってファイル名の頭を修正した18番目のファイル「18【18番目の事業所名】28（×××・月別集計.xls」が開いていたので、そこのところではないかと思いました。
デバッグ画面に入ると…
『.Sheets(ws.Name).Range(HANI(0)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd』
…がエラー個所として？黄色くなっていました。
自分なりに修正できれば…とエラー文言を頼りにネット検索してみましたが、具体的にどこが悪いのか判らず仕舞いでした。
どこに問題があるのか？どう修正すれば良いのか？…再度ご教授をお願いしたい次第です。
なお、１回目の実行時は上手くいったのですが、途中何度も（回数は数えていなかった）【クリップボードに大きな情報が…】のダイアログが出たので、さしたる根拠もなく自分勝手に判断し『いいえ』をクリックしていました。が、２回目以降はそうしたダイアログは出ませんでした。出るところまでプログラムの実行が進んでいなかったためかもしれませんが…。
また、ファイル名を修正したファイルのファイル名のつけ方はほかのファイルと同じで間違いなく頭を「18」に変えただけで、他の部分は、他のファイルと【】でくくられた事業所名が異なるだけです。
よろしくお願いいたします。
（ご面倒かけ太郎） 2016/03/11(金) 11:24
その理由が未だに良く判らないではいるのですが、
最初にVBAを実行して上手く行った後、集計用ブックを見ると、自分では設定していないシートの「sheet1」が出来、VBA終了時その「sheet1」が開かれていました。
自分で設定した訳ではないので、どうしてか？･･･と言う事は深く考えずに「sheet1」を削除したのでした。
その後自分なりにいろいろ調べ、いろいろ試して見ましたがいずれも上手く行かず、前コメントのとおりの状態が続いていました。
その後、いろいろ試していた時、VBAは途中で止まってしまうのですが、最後に止まる18番目のシートを見ると最後に「sheet1」がない事に気がつきました。そこで他の全てのブック（「01」から「17」まで）を見ると全てのブックに「sheet1」が付いていました。
理由は良く判らない（･･･とは言え、もしかして
『 With CreateObject("Scripting.Dictionary")
'対象フォルダの.xlsmブックを調べて、頭文字が設定値ならばブックのオブジェクトを
'Dictionaryのオブジェクトに入れる』と、
『For Each wb In OpWB
For Each ws In wb.Sheets
'形式を選択して貼付で、値・加算として張り付ける
ws.Range(HANI(0)).Copy
.Sheets(ws.Name).Range(HANI(0)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
ws.Range(HANI(1)).Copy
.Sheets(ws.Name).Range(HANI(1)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
Next ws
wb.Close
Next wb』
･･･を実行するにあたり、自動的に加算をするための一時的保管場所として「sheet1」が作られるのか？
ファイル名を間違って付けていて、後で修正した18番目のファイルは最初の実行の際使われていないため「sheet1」シートがないのか？
･･･と思い、ファイル名を修正した18番目のブックと集計用ブックに「sheet1」シートを追加したところ、【実行時エラー･･･】が出る事なく集計が出来ました。
なぜ、最初の･･･全く初めての実行の際には「sheet1」シートが無くても実行が上手く行ったのに、ファイル名の修正をしてからはエラーが出たのでしょうか？
それとも、考え方としては、「sheet1」シートが、ファイル名を変えたファイルに抱けなかったからエラーが出た。と言う事で、全てのファイルから「sheet1」シートを削除してしまえば、最初の実行と同じ様に上手く行くと言う事でしょうか？･･･実はこの事は、本日は時間切れで試してはいません。
全てのファイルが、シート構成において全く同一でないときちんと実行が完了しないと言う事なのでしょうか？
また、こう言ったVBAを実行すると必ず仮計算･･･加算をするためのデータを保持しておく「sheet1」シートが作成されると言うのが、エクセル･･･と言うかMSVBAの仕様なのでしょうか？
後、前コメントで上手く実行出来た最初の実行の途中で「途中何度も（回数は数えていなかった）【クリップボードに大きな情報が…】のダイアログが出た･･･」と書きましたが、結局は18回、各事業所の数字を合計する度に【クリップボードに大きな情報が…】のダイアログが出ると言う事ですかね。
そこで疑問が湧くのですが、もし選択肢の内【はい】を選択すると、最終的な集計結果は違ってくる･･･クリップボードに残された数字も含めて集計されてしまい、もっと膨大な数字となってしまうのでしょうか？
･･･であれば、集計結果が間違ってしまう事になりますが、この場合【いいえ】を選ぶのが正しいとしたら（実際にそうやると正しい集計結果になっていた）、VBAの途中で【クリップボードに大きな情報が…】のダイアログが出ても自動的に【いいえ】をクリックする1行（･･･或いは数行）の記述が必要なのではと思うのですが、どうなのでしょうか？
また、結果的に「sheet1」シートは自動的に作られるが、このVBAを毎月実行するに当たって、全てのブックに最初は「sheet1」シートが無くても問題ない。とすると、このVBA終了直前に？･･･開いた18事業所のブックを閉じる記述と、閉じる際に「sheet1」シートを削除する記述があるとより良いと思うのですが、そういう理解で良いのでしょうか？
この辺りの事は、VBAの事をネットで調べている今の段階では判らない状態です。
相変わらず沢山書いてしまい、却って判り難いかも知れませんが、コメント･･･と言うか解説をいただけるとありがたいです。
宜しくお願いいたします。
（ご面倒かけ太郎） 2016/03/11(金) 17:54
もうひとつ、自動的に作られた「sheet1」シートを削除するには、「 Activesheet.Delete 」を使えば良く、作業を終えた、各事業所のブックを閉じるには「 Workbooks("Book1").Close SaveChanges:= False 」を記述すれば良い事が判りましたが、この二つを、実際にどの辺りに、どの様に修正して挿入すると良いのかがまだ判っていません。
引き続き調べては見ますがご教示をお願いしたいと思います。
よろしくお願いいたします。
（ご面倒かけ太郎） 2016/03/13(日) 07:09
横から失礼。（特定の回答者さんを名指しておられるなか、恐縮です）
> 作業を終えた、各事業所のブックを閉じるには
> 「 Workbooks("Book1").Close SaveChanges:= False 」を記述すれば良い事が判りましたが、
> この二つを、実際にどの辺りに、
今でも
wb.Close
が実行されているはずですけど。
そのことではないんですか？
今のコードですと、対象ブックをすべて開いてから実行していますが、 一つ一つ開いては作業を行い、その都度閉じて行くスタイルもあります。 むしろそのほうが普通かもしれませんね。
なお、コード作成、検証の順序としても、 まずひとつのブックで対応してください。 ステップ実行したりして、内容を良く理解してから、 多数のブックを相手にするというアプローチをとったほうが安全ではないですか？
（γ） 2016/03/13(日) 08:37
金曜日土曜日見ていませんでした。すみません。 γさんフォローありがとうございます。
＞最初にVBAを実行して上手く行った後、集計用ブックを見ると、自分では設定していないシートの「sheet1」が出来、 Sheet1を作るコードは書いていませんので、勘違いでしょう。
＞なぜ、最初の･･･全く初めての実行の際には「sheet1」シートが無くても実行が上手く行ったのに、
For Each wb In OpWB
For Each ws In wb.Sheets
'形式を選択して貼付で、値・加算として張り付ける
ws.Range(HANI(0)).Copy
.Sheets(ws.Name).Range(HANI(0)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
ここの記述は、 被集計ブックの すべてのシートを 集計ブックの被集計ブックのシート名と同じところに加算して張り付ける という動作です。 つまり、被集計ブックにあって、集計ブックにないシートがあった時、 インデックスエラー ＝ シート名がありません というエラーになります。
＞もうひとつ、自動的に作られた「sheet1」シートを削除するには、 消すのは勝手ですが、作ってないので最初に消しておけばいいでしょう。
＞【クリップボードに大きな情報が…】のダイアログが出た･･･ これは手動操作でも表示されます。 コピー元のブックを閉じようとしたときに表示されるものですので、 貼り付け動作後に閉じていますから、いいえ で構いません。 wb.Closeの前後に DisplayAlerts = False・Trueを設定すればよいでしょう。
γさんもおっしゃるように、ステップ実行で各変数やプロパティの値を マウスで確認しながらデバッグしてみてください。
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_030.html
＞今のコードですと、対象ブックをすべて開いてから実行していますが、 ＞一つ一つ開いては作業を行い、その都度閉じて行くスタイルもあります。 ＞むしろそのほうが普通かもしれませんね。 確かに一つずつ開いてから作業するほうが普通ですね。 全て開いてからだと、メモリを沢山使う・エラーが出たときの原因が見つけにくい等あるので 今後気を付けます。 （今回メインのコードの読み易さと、開くブックの数など決め打ちだったので面倒で全部開きました・・・）
ところで・・・ 同じブックをコピーしているはずなのにおかしいですね？ シート構成が異なるなんて。 挙句、自動的にSheet1が作られるなんて決めつけられたら、 私さすがに怒りますよ？ （稲葉） 2016/03/13(日) 10:03
コメントをいただき有り難うございます。
「sheet1」と言うシートは、VBAの実行から自動的に作られる。･･･訳ではないんですね？･･･。
それでは、これも、私が最初にテスト用のブックを作成した際に、「sheet1」と言うシートを作っていたと言うミスだった訳ですね。
と言う事は、全てのブックから単に不要である「sheet1」と言うシートを削除してしまえば良い訳ですね！！判りました。
稲葉様には再三にわたり私のつまらないミスでご迷惑をお掛けしてしまいました。毎回ごご丁寧に案内をいただきながら申し訳ない事です。済みません。
それから、γ様からもご指摘いただいた【「 wb.Close 」 が実行されているはずですけど。･･･】について、やはり私がVBAのプログラムを良く見ていなかったための勘違いと言う事だった訳ですね。
プログラムが上手く実行出来なかった際、集計用以外のブックも開いていた事から、そう言うものなんだ･･･と思い込んでいた次第ですね。
正直情けない事で言葉もありません。
それでも、ご親切にいろいろ解説をいただき有り難うございます。
再度いただいた追加記述を、ご案内に沿って「 wb.Close 」の直前に挿入しテストを行い、現状と同じ様に動く様であれば、本番環境を作成させていただきます。
この集計作業は、今回ご案内いただいた実例の他に、シートの表のレイアウトが異なるもう一つのブックもあり、同じ様に集計をしなければなりません。この作成していただいたVBAプログラムがそこでも生かせる事になる訳で、本当に有り難く思います。
この業務･･･まさに実際の来年度の業務になる訳ですが、ずっと生かせる事になります。
職場の人々には良く判らないと思いますが、このプログラムの最後に作成者である稲葉様の名前を残しておきたいと思うのですが構いませんか？
或いは、残しておくサインがあればお教えいただければと思います。
少し悶々としていたところではありますが、今回も本当に有り難うございました。
（ご面倒かけ太郎） 2016/03/13(日) 16:49
＞職場の人々には良く判らないと思いますが、 後でどのような経緯でプログラム作成に至ったかわかるように、ここのURLを載せておくのが良いかと。
（稲葉） 2016/03/14(月) 08:26
…また、ミスをしたら、ご相談させてください。
よろしくお願いいたします。
（ご面倒かけ太郎） 2016/03/14(月) 09:01
現在引き続きご教示いただきたい事が出来ました。
それは･･･
１、「For Each ws In .Sheets」の「 .Sheets 」は、どうして「 Worksheet 」 or 「 Worksheets 」と記述していないのでしょうか？
「 .Sheets 」と「 Worksheet 」の違いはネットでいろいろ調べると載っているのですが、今ひとつどう言う場面で使い分けるのかが良く判りません。
２、「ws.Range(HANI(0) & "," & HANI(1)).ClearContents 」の記述についてですが、この「ws.Range(HANI(0) & "," & HANI(1)).ClearContents 」の解説をお願いしたいのです。
ネットでいろいろ調べては見たのですが、「 & "," & 」の部分の意味が良く判らず、全体としてどう言う事になっているのかが良く判っていません。ご案内をいただければと思い質問させていただきました。
･･･なぜこのような質問をしたのかは、先のコメントでもご案内したとおり、別のシートの表のレイアウトが異なるブック（ブックは18事業所分あり、ブックのシートの構成は同じで、集計のやり方も同じです）がありますが、コピーし加算して行く表の範囲が2範囲ではなく18範囲くらいあるので、最初に「集計を実行する前に、集計ブックに入力されたデータを一度リセットします。･･･」を実現するために、前述の「２」の質問のところを少し手直しする必要があると思ったためです。
例えば範囲が18カ所あるとしたら、「 HANI(0)」から「 HANI(17) 」までを、繰り返し「 & "," & 」でつないで列記すれば良いのでしょうか？
宜しくお願いいたします。
（ご面倒かけ太郎） 2016/03/14(月) 20:04
またまた横やり失礼。
> 1.「For Each ws In .Sheets」の「 .Sheets 」は、 > どうして「 Worksheet 」 or 「 Worksheets 」と記述していないのでしょうか？ > > 「 .Sheets 」と「 Worksheet 」の違いはネットでいろいろ調べると載っているのですが、 > 今ひとつどう言う場面で使い分けるのかが良く判りません。
差異はどんなものと書いてありますか？
> 2.「ws.Range(HANI(0) & "," & HANI(1)).ClearContents 」の記述についてですが、 > この「ws.Range(HANI(0) & "," & HANI(1)).ClearContents 」の解説をお願いしたいのです。
・HANI(0) の表す文字列
・ ","
・HANI(1) の表す文字列
を連結し、
Range("D12:Z98"，"AL12:BH88").ClearContents
として、その範囲を消去しています。
> 例えば範囲が18カ所あるとしたら、「 HANI(0)」から「 HANI(17) 」までを、繰り返し > 「 & "," & 」でつない で列記すれば良いのでしょうか？ それでもよいけど、
For k = 0 to 17
Range(HANI(k)).ClearContents
Next
とするのが、普通の感覚でしょうね。
(答え太郎 ことγ）
（γ） 2016/03/14(月) 23:05
私も横入りで1点だけ。
>>For Each ws In .Sheets
VBA 言語は基本的には、英語 で組み立てられています。 外国語の名詞には 単数形 と 複数形 があるものが多いですね。 日本人にはなじみが薄いのですが、単数形と複数形は、あきらかに異なる、で 外国人にとっては、両者は、完全な別物。
Car なら 1台の車 でしょうし、Cars なら、車がたくさんある。 駐車場に Car があるなら 1台しかない、Carsがあるなら たくさんある と、もう、これは感覚的なものですね。
横道にそれました。
このコードは、ws という変数、これは1枚のワークシートですが、これを 【どこかから順番に１まいずつとりだす】ということですね。
じゃぁ、取り出し先はどこでしょう。 ワークシートがたくさん集まっているところ、これを コレクション と呼びますが そこからですよね。
で、そのワークシートがたくさん集まっているコレクションに、何か名前をつけておこうということになります。 この名前は何でもよかったわけです。 hoge でも hogehoge でも。 でも、どうせつけるなら、それが何かということが、わかりやすい名前にしようねと。 MS の人たちは英語感覚の人たちですから、【当然のように】この名前を 複数形 の名詞にします。
逆に、彼らから見れば For Each ws In Worksheet ということだと、1枚のワークシートからなぜ1枚のワークシートを取り出すんだ！ 言葉遣いがおかしいだろ？ ということでしょうね。
だから、当然のように Worksheets というコレクション名をつけた。そういう名前なので、我々は、それをつかわなければいけないと まぁ、そういうことです。
（β） 2016/03/15(火) 08:19
コメントありがとうございます。
> 1.「For Each ws In .Sheets」の「 .Sheets 」は、 > どうして「 Worksheet 」 or 「 Worksheets 」と記述していないのでしょうか？ > 差異はどんなものと書いてありますか？
…ですが、「 .Sheets 」はいろんな種類のシートを指定？する時に使い、「 Worksheet 」 or 「 Worksheets 」は個別にシートを使う時に通常使う。…と言う事の様で、今回稲葉様に作成していただいたVBAプログラムでは、事前の私の説明不足等で、どんな種類のシートがあるか判然としないためあえて汎用？万能？の「 .Sheets 」にしたのかな？…と思っています。が、そうなのかどうなのかが判らない。と言うのが実際のところでした。（…コメントにもそう書けば良かったのですね…。）
また…
『
> 2.「ws.Range(HANI(0) & "," & HANI(1)).ClearContents 」の記述についてですが、 > この「ws.Range(HANI(0) & "," & HANI(1)).ClearContents 」の解説をお願いしたいのです。
・HANI(0) の表す文字列
・ ","
・HANI(1) の表す文字列
を連結し、
Range("D12:Z98"，"AL12:BH88").ClearContents
として、その範囲を消去しています。』
…についてのご案内ありがとうございます。
さらに…
『 それでもよいけど、
For k = 0 to 17
Range(HANI(k)).ClearContents
Next
とするのが、普通の感覚でしょうね。』
…についてのご教示ありがとうございます。
稲葉様にもこれまで散々お手数をお掛けしているところですが、こうして皆様にご教示いただきながら、実際のプログラムを見ながらいろいろ悪戦苦闘（…しています、実際…）する事で、これまでいくら参考のプログラムを見ても、いまひとつ理解が及ばないでいた事などが、少しずつ判って来た様な気がしています。
私にもプログラム以外では得意なもの（…頭を使う事ではなくどちらかと言うと筋肉を使う事ですが…）もあり、そうでない人を見ると、時にはどんくさいな…と思う事もあり、きっと今の私はそんなんだろうなと思っていますが、皆様に新設にしていただきありがたいです。
どうもありがとうございます。
また、引き続きよろしくお願いいたします。
（ご面倒かけ太郎） 2016/03/15(火) 09:11
書いている間に返事ありましたが、前の質問についてお答えしています。
もうほとんど先生方に回答されているので、補足するまでもないのですが・・・
１）これが、SheetとWorkSheetの違い、という意味であるなら・・・
Sheetには複数の種類があります。 その中に、WorkSheetという種類のシートがあると認識してください。
例えば、なんでも良いので新しいシートを右クリックして挿入を選ぶと、グラフ（Chart)とか Excel4.0マクロとか出てくると思います。 試しに、挿入＞グラフを選択して名前を「テスト」としてください。
VBEを開き、標準モジュールを追加して以下のコードをステップ実行で試してください。
Sub シートの選択テスト()
Dim gs As Chart
Dim ws As Worksheet
Set gs = Sheets("テスト") '○ SheetsはシートならばWorksheetでもグラフ（Chart）でも呼び出せる
Set gs = Charts("テスト") '○ もちろん、Charts（グラフ達）で名前を指定しても呼び出せる
Set gs = Worksheets("テスト") '× グラフ（Chart）はWorksheetではないので、Worksheetsでは呼び出せない
Set ws = Charts("テスト") '× 右辺は正しいが、左辺の変数はWorksheet型なので、入れられないエラー
End Sub
本題に戻ります。 ＞今ひとつどう言う場面で使い分けるのかが良く判りません。 通常グラフシートを使うことは無いと思います。 Worksheetだけあると想定して総当たりする場合に、グラフシートが混ざっていると For Each ws Sheets の段階で、「型が一致しません」というエラーになります。
For Each ws Worksheets このように訂正すれば、グラフシートが混ざっていてもエラーにはなりません。 しかし、先にも申し上げた通り、グラフシートを使っている人は、私は今まで見たことがありません。 なので、特に使い分ける必要が無く（と私は思う）、Sheetsを使っています。
２）＞「ws.Range(HANI(0) & "," & HANI(1)).ClearContents 」の解説
Rangeプロパティには指定の仕方が異なります。
一つは、引数を,で区切った場合は、カンマで区切られた範囲と範囲を含む矩形範囲を指定したとみなされます。
一方、文字列の中で区切った場合は、それぞれ単一のセルを指定したとみなされます。
以下のコードを実行し、違いを確かめてください。
Sub Rangeのテスト()
Range("A1", "D2").Interior.Color = vbRed
Range("A1,D2").Interior.Color = vbBlue
End Sub
ws.Range(HANI(0) & "," & HANI(1))の場合
ws.Range("D12:AA99" & "," & "AL12:BI89")
ws.Range("D12:AA99,AL12:BI89")
となり、独立した別々の範囲を指定したことになります。
ws.Range(HANI(0) & "," & HANI(1)) と
ws.Range(HANI(0) , HANI(1)) は、明らかに別ものであることを理解してください。
また、Rangeオブジェクトの引数で文字列を渡す場合、255文字を超えるとエラーになります。
ですので
＞例えば範囲が18カ所あるとしたら、「 HANI(0)」から「 HANI(17) 」までを、繰り返し
＞「 & "," & 」でつないで列記すれば良いのでしょうか？
の答えは、「ダメ」になります。
問題はそこだけではなく、コピーを張り付ける場所にもあります。
ご存知の通り、コピーは離れた複数範囲をまとめて張りつけることはできません。
Sub Copyのテスト()
Range("A1,C1").Copy
Range("D1,F1").PasteSpecial 'エラーになる
End Sub
だからと言って、以下のコードを何個も記述するのは馬鹿らしいので、γさんの言う通り、
関数化してしまいます。
ws.Range(HANI(0)).Copy
.Sheets(ws.Name).Range(HANI(0)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
ws.Range(HANI(1)).Copy
.Sheets(ws.Name).Range(HANI(1)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
ここの部分を
Dim i As Long'ここは冒頭のまとめて宣言しているところに
'以下をCopyのところと置き換える
For i = 0 To UBound(HANI)
ws.Range(HANI(i)).Copy
.Sheets(ws.Name).Range(HANI(i)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
Next i
ClearContentsの下りも、γさんの御指摘をもとに修正してみてください。
（稲葉） 2016/03/15(火) 09:58
＞ だからと言って、以下のコードを何個も記述するのは馬鹿らしいので、γさんの言う通り、 ＞ 関数化してしまいます。 訂正 関数化→ループ （稲葉） 2016/03/15(火) 10:00
そのあとの…
Range("A1", "D2").Interior.Color = vbRed Range("A1,D2").Interior.Color = vbBlue …で、「Range("A1,D2")」と「Range("A1", "D2")」の違いが判りました。
「 "," 」は「HANI(0)」と「HANI(1)」を「,」でつなぎ、「Range("A1,D2")」の様に範囲指定した。…と言う事ですね！
この応用について、γさんのご案内を基に、もう少し自分で考えてみます。…実は、早速「Dim k As Integer」で、「 k 」を宣言して単純に「 For k = 0 to 17 」とやったら、せっかく範囲について指定したのに範囲に関係なく項目名などすべてクリアされてしまいました。
また、稲葉様のご指摘のとおり…
＞例えば範囲が18カ所あるとしたら、「 HANI(0)」から「 HANI(17) 」までを、繰り返し ＞「 & "," & 」でつないで列記すれば良いのでしょうか？ …をやったら、 ＞の答えは、「ダメ」になります。
…のとおり、ダメになりました。
ここについても、再度ご教示いただいた「 i 」を宣言し修正してみます。
しかし、やはり難しい…。
（ご面倒かけ太郎） 2016/03/15(火) 10:46
＞As Integer の型宣言ですが、整数を扱うのであればLongで良いです。 http://www.banana-juice.com/VBA/Performance/Statement/IntLong.php
Integerを使う必要はありません。
余り難しいことはしていませんので、体系的に学べばいつの間にか簡単なことになってますよ。
ClearContentsについては、Unionメソッドで範囲を決めてしまってから値を消すなど 他にも考え方がありますが、当面（動作が遅くて仕事にならない）覚えなくても問題 ありません。使い方だけ下記に記します。 もっと現実的な例が出せればいいのですが、すみません。
Sub UNIONを使ってみる()
Dim i As Long
Dim r As Range
Dim HANI(3) As String
HANI(0) = "A1"
HANI(1) = "A3"
HANI(2) = "B2"
HANI(3) = "B4"
For i = 0 To UBound(HANI)
If r Is Nothing Then
Set r = Range(HANI(i))
Else
Set r = Union(r, Range(HANI(i)))
End If
Next i
r.Interior.Color = vbYellow
End Sub
他にも、HANI(0) & "," & HANI(1)は、わかりやすくするために記述していますが、 Joinを使って、カンマ区切りの文字列を取得することもできます。 今回は255文字制限があったため、使いません。
Sub JOINしてみる()
Dim HANI(3) As String
Dim AllHani As String
HANI(0) = "C1"
HANI(1) = "C3"
HANI(2) = "D2"
HANI(3) = "D4"
AllHani = Join(HANI, ",")
Range(AllHani).Interior.Color = vbGreen
End Sub
ステップ実行して、AllHaniの値がどのように格納されるか確認してください。
逆に、一々列挙が面倒な時は、Splitを使って文字列を配列にすることもできます。
またArrayを使って最初から配列として指定することもできます。
以下の例は、どちらも同じ（厳密にいえば型が違う）値が格納されます。
Sub Splitしてみる()
Dim HANI() As String
HANI = Split("A5:A10,B11:B15,C5:C10", ",")
End Sub
Sub Arrayしてみる()
Dim HANI() As Variant
HANI = Array("A5:A10", "B11:B15", "C5:C10")
End Sub
色々書きましたが、範囲を増やすうえで必要になる知識です。 後に残された人が読みやすいコードを書くよう、心掛けてみてください。
（稲葉） 2016/03/15(火) 11:48
言え、本当にありがとうございます。
…で、以下の様に書き直してみましたが、上手く行ってません。
正直、ひどい間違いをしているのだろうな…とは感じていますが…。
Option Explicit
'=====実行するプロシジャ===========================
Sub 集計開始()
'==================================================
Dim wb As Variant
Dim ws As Worksheet
Dim myWB As Workbook
Dim HANI(22) As String
Dim k As Long
Dim i As Long
'集計範囲を文字列で設定
HANI(0) = "D12:F177" ’ひどいですね。ただただ列挙しているだけです。
HANI(1) = "G12:H177" ’範囲は表の下の方にさらに合計を
HANI(2) = "J12:K177" ’出して貰いたいと言う部分があり、
HANI(3) = "M12:N177" ’全部で23もの範囲がありました。
HANI(4) = "P12:Q177"
HANI(5) = "S12:T177"
HANI(6) = "V12:W177"
HANI(7) = "Y12:Z177"
HANI(8) = "AB12:AC177"
HANI(9) = "AE12:AF177"
HANI(10) = "AH12:AI177"
HANI(11) = "AK12:AL177"
HANI(12) = "AN12:AO177"
HANI(13) = "AQ12:AR177"
HANI(14) = "AT12:AU177"
HANI(15) = "AW12:AX177"
HANI(16) = "AZ12:BA177"
HANI(17) = "BC12:BD177"
HANI(18) = "D197:J198"
HANI(19) = "D203:F204"
HANI(20) = "I203:K204"
HANI(21) = "AD198:AJ199"
HANI(22) = "AP203:AV204"
If MsgBox("集計を実行する前に、集計ブックに入力されたデータを一度リセットします。よろしいですか？", vbYesNo) = vbYes Then
With ThisWorkbook
For Each ws In .Sheets
On Error Resume Next ’２行下で【.SpecialCells(xlConstants, 23)】を加えた関係で追加しました。
For k = HANI(0) to HANI(22) ’←ここなんか違うんだろうな・・・と言う気がしています。
ws.Range(k).SpecialCells(xlConstants, 23).ClearContents ’←ここの【 ws.Range(k)…】なんてのも違う様な気がしています。また、シート構成に「毎月のAとBシートの合計が欲しい」と言われ、各月３シート構成に変更。３シート目（仮称「C」シートには「A」と「B」の合計式が入っていたので、値のみクリア出来る様に、ガンバリました。
Next
Next ws
For Each wb In OpWB
For Each ws In wb.Sheets
'形式を選択して貼付で、値・加算として張り付ける
For i = 0 To UBound(HANI) ’ここからは単に丸写しですが、それがいけないのか？と感じています。
ws.Range(HANI(i)).Copy
.Sheets(ws.Name).Range(HANI(i)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
Next i
Debug.Print ws.Name
Next ws
wb.Close
Next wb
End With
MsgBox "集計が完了しました"
End If
End Sub
'=====ブックのオブジェクト配列を作成する===========
Private Function OpWB() As Variant
'==================================================
Dim myDir As String
Dim F As String
Dim Nm As String
'集計するブック名の頭文字をカンマで区切る
Nm = "01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18"
'集計対象ブックが保管されているフォルダのパス
myDir = "C:\Users\増田\Desktop\test 求職・区別\"
With CreateObject("Scripting.Dictionary")
'対象フォルダの.xlsブックを調べて、頭文字が設定値ならばブックのオブジェクトを
'Dictionaryのオブジェクトに入れる
F = Dir(myDir & "*.xls")
Do While Len(F) > 0
If Nm Like "*" & Left(F, 2) & "*" Then
Set .Item(myDir & F) = Workbooks.Open(myDir & F, ReadOnly:=True)
End If
F = Dir()
Loop
OpWB = .items
End With
End Function
…だらだらと注釈も書きましたが、こんな具合です。
ミスや間違いをご指摘ください。
お願いいたします。
（ご面倒かけ太郎） 2016/03/15(火) 13:05
今度から、どのようにうまくいかないのか、説明してください。
＞On Error Resume Next これ、安易に取り入れてはいけません。 なぜいけないのかについては、以前ichinoseさんが投げかけた以下のスレッドが参考になります。 [[20150906185643]] 『On Error 〜ステートメントについて』（ ichinose）
ではどのように使えば良いのか、または使う必要があるのか？
＞３シート目（仮称「C」シートには「A」と「B」の合計式が入っていたので、値のみクリア出来る様〜〜
ということは、シート名に特定の文言が入っていれば、SpecialCellsを使う必要が無いわけですね？
For Each ws In .Sheets
If ws.Name Like "*C" Then 'シート名がCで終わる場合
'なにもしない
Else
'実際の処理
End If
Next ws
と記述することで、SpecialCellsは不要になるわけです。
＞For k = HANI(0) to HANI(22) 気持ちはよくわかります！！ がこれでは For k = "D12:F177" to "AP203:AV204" となってしまいます。 kはLong型、つまり数値ですよね？
なので、配列の最後の要素は何番か調べるためにUBound、 最初の要素は何番かを調べるためにLBoundがあります。 ですので For k = LBound(HANI) to UBound(HANI) が正解になります。
但し、UBoundが返す数値は「要素がn個ある」ではないことに注意してください。 あくまで最後の要素は何番目か、を調べるものです。 以下のコードを実行し、イミディエイトウィンドウを確認してください。
Sub UBoundLBound()
Dim a(5)
Dim b(1 To 5)
Debug.Print LBound(a) & " to " & UBound(a)
Debug.Print LBound(b) & " to " & UBound(b)
End Sub
詳細は下記のサイトが参考になります。
http://www.relief.jp/itnote/archives/excel-vba-array-elements-count.php
気づいた点は2点だけです。
（稲葉） 2016/03/15(火) 15:56
これから自分なりに考えて？･･･悩んでみます。
取り敢えずお礼を申し上げます。
（ご面倒かけ太郎） 2016/03/15(火) 18:45
こんにちは。
本筋の話ではないのですが、WorkSheetsとSheetにかんする 稲葉さんの 2016/03/15(火) 09:58 のご説明のうち、この部分
> Worksheetだけあると想定して総当たりする場合に、グラフシートが混ざっていると > For Each ws Sheets > の段階で、「型が一致しません」というエラーになります。
グラフシートが一番左にあるときだけ For Each ws in Sheets の段階でエラーになります。 これは、ワークシート型の変数wsにグラフシートを格納しようとしたためです。 もしワークシートが一番左であれば For Each ws in Sheets は問題なく通過します。 そのあとで、グラフシートの順番になったときに Next ws のところでエラーが出ます。
このコードは面白くって、ループ全体にon error resume next してもエラートラップできません。 ステートメントに対してはエラートラップは効かないのか？
で、エラーが出たらデバッグをクリックして Next ws が黄色くなっているのを見て F5を押すと、コードが再開します。次のシートがグラフシートだったら再度エラーが出ます。 ふつう、エラーが出たらＦ５を押しても進まないものなんですけど。
で、エラーが出たらデバッグをクリックして Next ws が黄色くなっているのを見て F5を押すと、コードが再開します。次のシートがグラフシートだったら再度エラーが出ます。 で、エラーが出たらデバッグをクリックして Next ws が黄色くなっているのを見て F5を押すと、コードが再開します。次のシートがグラフシートだったら再度エラーが出ます。 で、エラーが出たらデバッグをクリックして Next ws が黄色くなっているのを見て F5を押すと、コードが再開します。次のシートがグラフシートだったら再度エラーが出ます。
最終的に、シートの数だけ回すとコードが終了します。 いろいろ不思議です。不思議で楽しいです。 （＾＾
' ' > しかし、先にも申し上げた通り、グラフシートを使っている人は、私は今まで見たことがありません。 じつはわたし、使っています（＾＾； グラフだけをしっかりど真ん中でみたいときがあるのです。正直、データは少々アレでもどうでもいいという。
（ 佳 ） 2016/03/15(火) 20:21
>Range("D12:Z98"，"AL12:BH88").ClearContents
>として、その範囲を消去しています。
うーむ手がすべったね。
単に文字列連結だから、
Range("D12:Z98,AL12:BH88").ClearContents
と書かないと説明にならなかった。
もうどうでもいい話になっていると思うが、一応間違いは訂正しておかないと。
（γ） 2016/03/15(火) 20:55
ともに貴重なご案内有り難うございます。
まだ一通り読んだだけでは、なるほど！！と直ぐに腑に落ちる程技量がないのですが、数式を残し値だけをクリア出来れば良いと言う事一つとっても奥の深さを感じているところです。
今日はもういろいろいじれないのですが、明日また職場に行ったらいじってみようと思います。
でも、きっと直ぐに「上手く動いた！」と言う事にはなりそうにないので、その際にはまたお助けコメントをアップさせていただきたいと思います。
それから、どの様に上手く行かないのか、より分かり易く具体的に書きたいと思います。
（ご面倒かけ太郎） 2016/03/15(火) 23:02
佳 さん ご指摘ありがとうございます。 （使ったことないので、説明するとすぐボロが出ますね・・・） にしても、不思議な動きですねぇ。
結局グラフ使うときって、説明用の資料が多いので、結局埋め込みが多く Sheetsで回してもエラーに当たったことないんですよね。 ということは、For Each ws In Worksheetsを常用していらっしゃる？？
（稲葉） 2016/03/16(水) 09:24
If MsgBox("集計を実行する前に、集計ブックに入力されたデータを一度リセットします。よろしいですか？", vbYesNo) = vbYes Then
With ThisWorkbook
For Each ws In .Sheets
If ws.Name Like "*月別合計" Then 'シート名が月別合計で終わる場合
'なにもしない
Else
For k = LBound(HANI) to UBound(HANI) 'クリア範囲を指定
ws.Range(k).ClearContents
End If
Next ws
For Each wb In OpWB
‘以下省略
』
…が、【コンパイルエラー：End Ifに対応するIfブロックがありません】と出てしまいました。
自分なりに、「 Else 」の前にIf文の条件が真の時の処理が記述されていないためだろうと考えてみました。色々調べたのですが、「 Else 」の後だと記述の省略が出来そうですが、この場合真の時の処理の記述が必要なのか…との考えに至り、それならばIf文の書き方を一致した場合ではなく、一致しなかった場合に下記か会えれば良いだろうと考え次のとおりにしてみたのですが
『
If ws.Name <> "*月別合計" Then
For k = LBound(HANI) to UBound(HANI) 'クリア範囲を指定
ws.Range(k).ClearContents
Next ws
For Each wb In OpWB
‘以下省略
』
…と書き直してみましたが、今度は別の【コンパイルエラー：Nextで指定された変数の参照が不正です】と出て、デバッグ画面に入ると『 Next ws 』の「 ws 」が水色に反転していました。
今回の修正でいじったのは…
『
With ThisWorkbook
For Each ws In .Sheets
』
と
『
Next ws
For Each wb In OpWB
For Each ws In wb.Sheets
』
の間だけなので、特に２回目のコンパイルエラーは何故そうなるのか良く判りません。
相変わらず間抜けな事をやっている様で情けありませんが、どこがどう悪いのでしょうか？
（ご面倒かけ太郎） 2016/03/16(水) 13:49
???さんから指摘されている通りですが・・・
インデントを意識して使っていますか？ インデントは登ったら、順番に降りてきます。 落ち着いて考えればすぐ気づくことなので、インデントがきれいに並んでいるか もう一度見直してみてください。
Sub 再現してみる()
Dim i As Long
Dim k As Long
For i = 1 To 10
If i Mod 2 = 1 Then
For k = 1 To 10
Debug.Print i * k
'Next k
End If
Next i
End Sub
（稲葉） 2016/03/16(水) 14:08
早々にご指摘ありがとうございます。
早速「 Next k 」を追記、次にもう一度デバッグ画面でインデントを確かめました。
そうしたら、動き始めたのですが、今度は【実行時エラー：'Range'メソッドは失敗しました'Worksheet'オブジェクト】とエラーダイアログが出ました。
デバッグ画面に入ると…
『ws.Range(k).ClearContents』が黄色く反転していました。
マクロの編集画面を開き「ステップイン（何とかF8で試せるようになりました）」で試してみてもそこで止まりました。
このほかにもいろいろありそうですが、今のところ気が付きません。
インデントも含め修正し直したものを載せておきます。
『
Option Explicit
'=====実行するプロシジャ===========================
Sub 集計開始()
'==================================================
Dim wb As Variant
Dim ws As Worksheet
Dim myWB As Workbook
Dim HANI(22) As String
Dim k As Long
Dim i As Long
'集計範囲を文字列で設定
HANI(0) = "D12:F177"
HANI(1) = "G12:H177"
HANI(2) = "J12:K177"
HANI(3) = "M12:N177"
HANI(4) = "P12:Q177"
HANI(5) = "S12:T177"
HANI(6) = "V12:W177"
HANI(7) = "Y12:Z177"
HANI(8) = "AB12:AC177"
HANI(9) = "AE12:AF177"
HANI(10) = "AH12:AI177"
HANI(11) = "AK12:AL177"
HANI(12) = "AN12:AO177"
HANI(13) = "AQ12:AR177"
HANI(14) = "AT12:AU177"
HANI(15) = "AW12:AX177"
HANI(16) = "AZ12:BA177"
HANI(17) = "BC12:BD177"
HANI(18) = "D197:J198"
HANI(19) = "D203:F204"
HANI(20) = "I203:K204"
HANI(21) = "AD198:AJ199"
HANI(22) = "AP203:AV204"
If MsgBox("集計を実行する前に、集計ブックに入力されたデータを一度リセットします。よろしいですか？", vbYesNo) = vbYes Then
With ThisWorkbook
For Each ws In .Sheets
If ws.Name Like "*保護困窮" Then
Else
For k = LBound(HANI) To UBound(HANI)
ws.Range(k).ClearContents
Next k
End If
Next ws
For Each wb In OpWB
For Each ws In wb.Sheets
'形式を選択して貼付で、値・加算として張り付ける
For i = 0 To UBound(HANI)
ws.Range(HANI(i)).Copy
.Sheets(ws.Name).Range(HANI(i)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
Next i
Debug.Print ws.Name
Next ws
wb.Close
Next wb
End With
MsgBox "集計が完了しました"
End If
End Sub
'=====ブックのオブジェクト配列を作成する===========
Private Function OpWB() As Variant
'==================================================
Dim myDir As String
Dim F As String
Dim Nm As String
'集計するブック名の頭文字をカンマで区切る
Nm = "01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18"
'集計対象ブックが保管されているフォルダのパス
myDir = "C:\Users\増田\Desktop\test 求職・区別\"
With CreateObject("Scripting.Dictionary")
'対象フォルダの.xlsブックを調べて、頭文字が設定値ならばブックのオブジェクトを
'Dictionaryのオブジェクトに入れる
F = Dir(myDir & "*.xls")
Do While Len(F) > 0
If Nm Like "*" & Left(F, 2) & "*" Then
Set .Item(myDir & F) = Workbooks.Open(myDir & F, ReadOnly:=True)
End If
F = Dir()
Loop
OpWB = .items
End With
End Function
』
（ご面倒かけ太郎） 2016/03/16(水) 14:39
kはただのループ変数なので、セル範囲を表していませんよね？ ws.Range(HANI(k)).ClearContents では？
というように、ひとつひとつバグを潰していくのがデバッグです。他人にやらせる作業ではないですよ〜。
どんなに時間がかかろうと、そこは自力解決しましょう。
（???） 2016/03/16(水) 15:20
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.