Excel VBA質問箱 IV

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

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


11074 / 13644 ツリー ←次へ | 前へ→

【18165】行数が不定の集計処理について ackkn 04/9/16(木) 21:15 質問[未読]
【18166】Re:行数が不定の集計処理について Kein 04/9/16(木) 22:08 回答[未読]
【18167】Re:行数が不定の集計処理について ackkn 04/9/16(木) 22:49 お礼[未読]
【18168】Re:行数が不定の集計処理について ackkn 04/9/16(木) 23:04 質問[未読]
【18171】Re:行数が不定の集計処理について Kein 04/9/17(金) 1:46 発言[未読]
【18189】Re:行数が不定の集計処理について ackkn 04/9/17(金) 17:08 発言[未読]
【18207】Re:行数が不定の集計処理について Kein 04/9/18(土) 0:31 回答[未読]
【18208】Re:行数が不定の集計処理について Kein 04/9/18(土) 0:34 発言[未読]
【18213】Re:行数が不定の集計処理について ackkn 04/9/18(土) 11:47 回答[未読]
【18217】Re:行数が不定の集計処理について ackkn 04/9/18(土) 14:02 回答[未読]
【18223】Re:行数が不定の集計処理について Kein 04/9/18(土) 18:25 回答[未読]
【18224】Re:行数が不定の集計処理について Kein 04/9/18(土) 18:32 発言[未読]
【18249】Re:行数が不定の集計処理について ackkn 04/9/19(日) 14:41 回答[未読]
【18256】Re:行数が不定の集計処理について Kein 04/9/19(日) 20:11 回答[未読]
【18257】Re:行数が不定の集計処理について ackkn 04/9/19(日) 23:22 回答[未読]
【18259】Re:行数が不定の集計処理について Kein 04/9/20(月) 0:56 回答[未読]
【18263】Re:行数が不定の集計処理について ackkn 04/9/20(月) 6:38 お礼[未読]
【18289】Re:行数が不定の集計処理について ackkn 04/9/20(月) 17:58 質問[未読]
【18293】Re:行数が不定の集計処理について Kein 04/9/20(月) 22:18 回答[未読]
【18299】Re:行数が不定の集計処理について ackkn 04/9/20(月) 23:42 質問[未読]
【18301】Re:行数が不定の集計処理について Kein 04/9/21(火) 2:13 回答[未読]

【18165】行数が不定の集計処理について
質問  ackkn  - 04/9/16(木) 21:15 -

引用なし
パスワード
   どなたかご教示下さい、よろしくお願いします。
行数が不定の表で、ある列の集計をその列の最下行に出すのに以下のコードを書きましたが、もっとスマートな方法をどなたか教えて下さい。

 A列  B列   C列  D列  E列
 チェック 品種  数量 ケース数 入数
    つがる 280  3   60
    ふじ  570  8   80
    スター 480  12   40
    合 計 1330  23 ← この行を出す


  Dim Adr1 As String
  Dim Adr2 As String
  '--- C列の集計
  Adr1 = ActiveSheet.Range("C4").Address
  Adr2 = ActiveSheet.Range("C65536").End(xlUp).Address
  ActiveSheet.Range("B65536").End(xlUp).Offset(1, 0).Value = "合 計"
  ActiveSheet.Range("C65536").End(xlUp).Offset(1, 0).Formula = "=SUM(" + Adr1 + ":" + Adr2 + ")"
  '--- D列の集計(数式が入っている為、C列で最下行を見つけている)
  Adr1 = ActiveSheet.Range("D4").Address
  Adr2 = ActiveSheet.Range("C65536").End(xlUp).Offset(-1, 1).Address
  ActiveSheet.Range("C65536").End(xlUp).Offset(0, 1).Formula = "=SUM(" + Adr1 + ":" + Adr2 + ")"

【18166】Re:行数が不定の集計処理について
回答  Kein  - 04/9/16(木) 22:08 -

引用なし
パスワード
   Dim Rcnt As Long

Rcnt = Range("C65536").End(xlUp).Row
Cells(Rcnt + 1, 2).Value = "合 計"
Cells(Rcnt + 1, 3).Resize(, 3).Formula = "=SUM(C$4:C$" & Rcnt & ")"

で、どうでしょーか ?

【18167】Re:行数が不定の集計処理について
お礼  ackkn  - 04/9/16(木) 22:49 -

引用なし
パスワード
   ▼Kein さん:
>Dim Rcnt As Long
>
>Rcnt = Range("C65536").End(xlUp).Row
>Cells(Rcnt + 1, 2).Value = "合 計"
>Cells(Rcnt + 1, 3).Resize(, 3).Formula = "=SUM(C$4:C$" & Rcnt & ")"
>
>で、どうでしょーか ?
Kein さん、早速のレスありがとうございました。
見事です!、ここまでいくと気持ちいいですね!、大変勉強になりました。

これからも、よろしくお願い致します。

【18168】Re:行数が不定の集計処理について
質問  ackkn  - 04/9/16(木) 23:04 -

引用なし
パスワード
   ▼Kein さん:
>Dim Rcnt As Long
>
>Rcnt = Range("C65536").End(xlUp).Row
>Cells(Rcnt + 1, 2).Value = "合 計"
>Cells(Rcnt + 1, 3).Resize(, 3).Formula = "=SUM(C$4:C$" & Rcnt & ")"
>
>で、どうでしょーか ?
Kein さん、すいませんが、もう一つ教えて下さい。
今回の表で、B列、C列が手入力なんですが、集計した後、このシートをコピーしてシート内の値をクリアする方法なんですが、今回の集計で最下行の計算式だけが変わっているのを直ぐ上の計算式に戻して、他の値はクリアする方法を教えて下さい、よろしくお願いします。

【18171】Re:行数が不定の集計処理について
発言  Kein  - 04/9/17(金) 1:46 -

引用なし
パスワード
   >最下行の計算式だけが変わっているのを直ぐ上の計算式に戻して
この意味が分かりませんが・・。具体的な運用の仕方を書いてみて下さい。

【18189】Re:行数が不定の集計処理について
発言  ackkn  - 04/9/17(金) 17:08 -

引用なし
パスワード
   ▼Kein さん:
>>最下行の計算式だけが変わっているのを直ぐ上の計算式に戻して
>この意味が分かりませんが・・。具体的な運用の仕方を書いてみて下さい。
Kein さん、こんにちは!
説明が悪くて申し訳ありません。m(_ _)m
最初の質問の際に書いたシートは、倉庫の入庫管理なんです。
それで、日々シート(シート名が日付(9月17日等))に入庫した品名と数量を記入していき、1日の最後に集計(Keinさんの技)する訳です。

 で、本当にやりたい運用は、朝、ブックを開いた時点で、その日(システム日付)のシートが存在しない場合は、原紙となる未記入シートをブック内に持って置いて、そのシートをコピーして名前にその日の日付を付けて開き、作業を開始する。また、その日(システム日付)のシートが存在する場合(中断等で保存後再開する場合)は、そのシートを開いて作業を続行する。以上の事をやりたいのです。

 が、Kein さんから「具体的な運用の仕方を書いてみて下さい。」って言われてハッ!と気付いたんですが、現在作業者が日々行っている操作をそのままマクロ化しようとしていたんです。で、Kein さんの技で集計までは行ったんですが、次にその日の最後(集計後)に集計したシートをコピーして、その後値をクリアしてシート名に明日の日付を付けて準備完了なのが、今の作業者の操作なんです。
ところが、その値をクリアする際に、Kein さんの技で集計した箇所が従来の計算式から=SUM...の式に変わっているので、そこを従来の計算式に戻さなければと思った訳です。(恥ずかしい)
解って頂けたでしょうか?、出来ましたら、本当にやりたい運用のやり方を教えて下さい、よろしくお願いします。

【18207】Re:行数が不定の集計処理について
回答  Kein  - 04/9/18(土) 0:31 -

引用なし
パスワード
   えっとですね・・
>次にその日の最後(集計後)に集計したシートをコピーして、その後値をクリアして
>シート名に明日の日付を付けて準備完了
という現在の作業形態を "止めて"
>朝、ブックを開いた時点で、その日(システム日付)のシートが存在しない場合は、
>原紙となる未記入シートをブック内に持って置いて、そのシートをコピーして名前に
>その日の日付を付けて開き、作業を開始する。
>また、その日(システム日付)のシートが存在する場合(中断等で保存後再開する場合)
>は、そのシートを開いて作業を続行する
という処理形態に移行したい、ということなんですね ?
なんか殆ど仕事の依頼みたいだけど、まぁいいです。
これはまず
>その日(システム日付)のシートが存在しない場合
を無くすことが第一のポイントでしょう。それはマクロを使えばわけなく出来ます。
>原紙となる未記入シート
これはつまり、テンプレートのようなもので、項目とか罫線などの書式を作ってあって
データだけが未記入のシートなわけですね ? 
それなら当月一ヶ月分の、新しいブックを作成するマクロを提示しましょう。

Sub ThisMonth_Make_NewBook()
  Dim MkFile As String
  Dim Ans As Integer, Scnt As Integer, NewS As Integer
  Dim SDay As Date
  Dim WS As Worksheet
 
  MkFile = Application.DefaultFilePath & _
  "\" & Month(Date) & "月.xls"
  If Dir(MkFile) <> "" Then
   Ans = MsgBox("今月のブックは既に存在します" & vbLf & _
   "削除して新規にブックを作成しますか", 36)
   If Ans = 7 Then Exit Sub
  End If
  NewS = Day(DateSerial(Year(Date), Month(Date) + 1, 1) - 1)
  SDay = DateSerial(Year(Date), Month(Date), 1)
  With Application
   Scnt = .SheetsInNewWorkbook
   .SheetsInNewWorkbook = NewS
   .ScreenUpdating = False
  End With
  Workbooks.Add
  With ActiveWorkbook
   For Each WS In .Worksheets
     WS.Name = CStr(Format(SDay, "m月d日"))
     SDay = SDay + 1
   Next
   ThisWorkbook.Sheets("Mytemplate").Copy Before:=.Worksheets(1)
   .Sheets.FillAcrossSheets .Sheets("Mytemplate").UsedRange
   .Sheets("Mytemplate").Visible = False
   .SaveAs Application.DefaultFilePath & "\" & Month(Date) & "月.xls"
   .Close
  End With
  With Application
   .ScreenUpdating = True
   .SheetsInNewWorkbook = Scnt
  End With
End Sub

これをどこかのブックに入れて、"Mytemplate" という仮のシート名を実際の
>原紙となる未記入シート
に変更して実行してみて下さい。これで記入する日のシートが見つからない、
という事態が避けられるのだから、ほぼ解決すると思います。ただ、質問の内容から
推測するに、月単位にブックを作っていくのでなく、一つのブックを使いまわしたい
みたいな感じですね・・。それならそれで、また別のコードを考えてみますから
説明して下さい。
あと、最初の質問の「合計を最終入力行の下に入れる」という処理に関しては、
原紙となる未記入シートに、データの入力可能な範囲を充分にとって雛型の表を
作り、その表の最終行に予め数式を入れておけば良いでしょう。そうすると、
どのシートも一定の行に数式がある形になるから、最後に当月の集計をするにも
3D参照(串刺し計算)の数式とか、統合機能などが使えて便利なのです。
よく検討してみてください。

【18208】Re:行数が不定の集計処理について
発言  Kein  - 04/9/18(土) 0:34 -

引用なし
パスワード
   >.SaveAs Application.DefaultFilePath & "\" & Month(Date) & "月.xls"
のところは

.SaveAs MkFile

で、結構です。変数の中身と一緒ですから。

【18213】Re:行数が不定の集計処理について
回答  ackkn  - 04/9/18(土) 11:47 -

引用なし
パスワード
   ▼Kein さん:
Kein さん、こんにちは。
レス、(ありがとうございます)3 <- 階乗のつもり
.....
>>シート名に明日の日付を付けて準備完了
>という現在の作業形態を "止めて"
.....
>という処理形態に移行したい、ということなんですね ?
>なんか殆ど仕事の依頼みたいだけど、まぁいいです
..
本当に申し訳ありません。m(_ _)m
しかし、初心者ながら丸投げなんてする気は毛頭なかったんですよ
(本当に)、でも結果的にそんな風になっちゃいましたよねー
改めて、申し訳ありません。m(_ _)m
まずは、お礼の一報と思って書きました。
>それなら当月一ヶ月分の、新しいブックを作成するマクロを提示しましょう。
でですね、下記マクロの件ですけど、まずは実際に動かして見ます。
(何せ、初心者なもんですから)
また申し訳ないんですが、簡単な流れを説明して頂けると助かります。(^^;)
>推測するに、月単位にブックを作っていくのでなく、一つのブックを使いまわし
>たいみたいな感じですね・・。それならそれで、また別のコードを考えてみます
>から説明して下さい。
最終的には月単位で纏めるのですが、実は、ちょっと事情があって(情けない)...
と言うのは、今回の倉庫(外部)にあるPCが古いのと、外部の為、日々のデータの受け渡しがFDなんですよ、で、収まるのがせいぜい2〜3日分ってとこだと思います。
で、日々毎朝その日分を作っていきながら回す運用を考えていました。

>Sub ThisMonth_Make_NewBook()
>  Dim MkFile As String
>  Dim Ans As Integer, Scnt As Integer, NewS As Integer
>  Dim SDay As Date
>  Dim WS As Worksheet
> 
>  MkFile = Application.DefaultFilePath & _
>  "\" & Month(Date) & "月.xls"
>  If Dir(MkFile) <> "" Then
>   Ans = MsgBox("今月のブックは既に存在します" & vbLf & _
>   "削除して新規にブックを作成しますか", 36)
>   If Ans = 7 Then Exit Sub
>  End If
>  NewS = Day(DateSerial(Year(Date), Month(Date) + 1, 1) - 1)
>  SDay = DateSerial(Year(Date), Month(Date), 1)
>  With Application
>   Scnt = .SheetsInNewWorkbook
>   .SheetsInNewWorkbook = NewS
>   .ScreenUpdating = False
>  End With
>  Workbooks.Add
>  With ActiveWorkbook
>   For Each WS In .Worksheets
>     WS.Name = CStr(Format(SDay, "m月d日"))
>     SDay = SDay + 1
>   Next
>   ThisWorkbook.Sheets("Mytemplate").Copy Before:=.Worksheets(1)
>   .Sheets.FillAcrossSheets .Sheets("Mytemplate").UsedRange
>   .Sheets("Mytemplate").Visible = False
>   .SaveAs Application.DefaultFilePath & "\" & Month(Date) & "月.xls"
>   .Close
>  End With
>  With Application
>   .ScreenUpdating = True
>   .SheetsInNewWorkbook = Scnt
>  End With
>End Sub

>あと、最初の質問の「合計を最終入力行の下に入れる」という処理に関しては、
>原紙となる未記入シートに、データの入力可能な範囲を充分にとって雛型の表を
>作り、その表の最終行に予め数式を入れておけば良いでしょう。そうすると、
>どのシートも一定の行に数式がある形になるから、最後に当月の集計をするにも
>3D参照(串刺し計算)の数式とか、統合機能などが使えて便利なのです。
>よく検討してみてください。
最終的には、月での集計もあるんですよねー...
でも、入庫量が日々まちまちで、多い日は数千ケース、少ない日は数十ケースてな感じなんで、雛形が大きな表になってしまい、まして、日々、シートを印刷までしているので、ここも頭が痛いんです。
でも、1歩づつ進めようと思っています、本当に勝手ばかり言いますが、また知恵をお貸し下さい、よろしくお願いします。m(_ _)m

【18217】Re:行数が不定の集計処理について
回答  ackkn  - 04/9/18(土) 14:02 -

引用なし
パスワード
   ▼ackkn さん:
>▼Kein さん:
Kein さん、こんにちは。
>>それなら当月一ヶ月分の、新しいブックを作成するマクロを提示しましょう。
>>でですね、下記マクロの件ですけど、まずは実際に動かして見ます。
動かして見て、感心していたのですが、出来た9月.xlsを開いてみて以下の問題点が見つかりました。
1)原紙のシートモジュールと標準モジュールが各シートにコピーされていませ
  ん。
2)普通にシートタグを右クリックして[移動またはコピー]でコピーすると、セル
  幅、高さもきれいにコピーされますが、今回の各シートはセル幅、高さともコ
  ピーされていません。
まずは、結果報告までと、私の知恵では何ともなりそーにありません。
引き続き、知恵は絞ります...
何卒、よろしくお願いします。m(_ _)m

【18223】Re:行数が不定の集計処理について
回答  Kein  - 04/9/18(土) 18:25 -

引用なし
パスワード
   >外部の為、日々のデータの受け渡しがFD
マシン間の距離とか導入コストの問題などもありますが、出来たらピアツーピアでも
いいから、LANを構築した方が楽ですよ。そうすればマクロでデータを引っ張ることも
出来るので
>日々毎朝その日分を作っていきながら回す
という作業から、開放されそうだからです。
>でも、入庫量が日々まちまちで、多い日は数千ケース、少ない日は数十ケースてな
>感じなんで、雛形が大きな表になってしまい、まして、日々、シートを印刷まで
>しているので、ここも頭が痛いんです
この場合「データ未入力の行を一気に非表示にする」というマクロを組むだけです。
例えば B列を基準とするなら

Sub R_Hidden_Change()
  Static Hck As Boolean
 
  On Error Resume Next
  If Hck = False Then
   If WorksheetFunction.CountA(Range("B:B")) = 0 Then
     MsgBox "B列に値がありません", 48: Exit Sub
   End If
   Range("B1", Range("B65536").End(xlUp)) _
   .SpecialCells(4).EntireRow.Hidden = True
   Hck = True
  Else
   Cells.EntireRow.Hidden = False
   Hck = False
  End If
End Sub

これを実行する度に、アクティブシートの空白行の表示・非表示が切り替えられます。
列のデータを合計する数式が、B列最終入力行(5000でも10000でも)にあれば、
項目と合計との間の空白が処理されるわけです。合計値の目視確認だけでなく、
印刷時にも非表示行は印刷されません。プレビューで確認してみて下さい。
>原紙のシートモジュールと標準モジュールが各シートにコピーされていません。
シートを丸ごとコピーすると、シートモジュールのコードは付いてくるはずです。
こちらでもテストで確認しています。標準モジュールについては無理ですから、
まずVBE画面でコピーしたいモジュールをアクティブにして「ファイル」「ファイルの
エクスポート」で任意のフォルダーに保存して下さい。そのフルパスを下のコードの
定数 Mdl に指定します。するとコードによってActiveWorkbookにインポートされます。
ついでに他のモジュールも、バックアップしておくと良いでしょう。予期せずブックが
破損したときに、バックアップファイルがあると助かりますよ。
>普通にシートタグを右クリックして[移動またはコピー]でコピーすると、
>セル幅、高さもきれいにコピーされますが、今回の各シートはセル幅、高さとも
>コピーされていません。
これはこちらでも確認しました。UsedRangeでコピーしていたためのようです。
Cells全体をコピーするコードに変えた結果、行高・列幅も反映されています。

↓の改造したコードで試してみて下さい。結果を直ぐにチェックできるよう、
ブックの Closeメソッドはコメントにしています。本番では "'" を外します。
なお、実行前に原紙シート名の変更、およびモジュールのエクスポートと定数値の変更
をお忘れなく。

Sub ThisMonth_Make_NewBook2()
  Dim MkFile As String
  Dim Ans As Integer, Scnt As Integer, NewS As Integer
  Dim SDay As Date
  Dim WS As Worksheet
  Const Mdl As String = _
  "C:\Documents and Settings\User\My Documents\モジュール\Module1.bas"
 
  MkFile = Application.DefaultFilePath & _
  "\" & Month(Date) & "月.xls"
  If Dir(MkFile) <> "" Then
   Ans = MsgBox("今月のブックは既に存在します" & vbLf & _
   "削除して新規にブックを作成しますか", 36)
   If Ans = 7 Then Exit Sub
  End If
  NewS = Day(DateSerial(Year(Date), Month(Date) + 1, 1) - 1)
  SDay = DateSerial(Year(Date), Month(Date), 1)
  With Application
   Scnt = .SheetsInNewWorkbook
   .SheetsInNewWorkbook = NewS
   .ScreenUpdating = False
  End With
  Workbooks.Add
  With ActiveWorkbook
   For Each WS In .Worksheets
     WS.Name = CStr(Format(SDay, "m月d日"))
     SDay = SDay + 1
   Next
   ThisWorkbook.Sheets("test").Copy Before:=.Worksheets(1)
   .Sheets.FillAcrossSheets .Sheets("test").Cells
   .Sheets("test").Visible = False
   .VBProject.VBComponents.Import Mdl
   .SaveAs MkFile
   '.Close
  End With
  With Application
   .ScreenUpdating = True
   .SheetsInNewWorkbook = Scnt
  End With
End Sub

【18224】Re:行数が不定の集計処理について
発言  Kein  - 04/9/18(土) 18:32 -

引用なし
パスワード
   ちょっと修正します。

If Dir(MkFile) <> "" Then
  Ans = MsgBox("今月のブックは既に存在します" & vbLf & _
  "削除して新規にブックを作成しますか", 36)
  If Ans = 6 Then
   Kill MkFile
  Else
   Exit Sub
  End If
End If

と、して下さい。

【18249】Re:行数が不定の集計処理について
回答  ackkn  - 04/9/19(日) 14:41 -

引用なし
パスワード
   ▼Kein さん:
Kein さん、こんにちは。
>LANを構築した方が楽ですよ。そうすればマクロでデータを引っ張ることも...
私も前の会社では、LANが当たり前の世界に居たので、歯痒い程解っているのですが、何せ業界が変わり、中小になると、これが現実なんです。(辛い!(T_T))
また、今回は外部(事務所から12〜3Km)倉庫なので簡単にはいきません。
>>日々毎朝その日分を作っていきながら回す
>という作業から、開放されそうだからです。
されません、ひとまず、毎朝その日分を作っていきながら回さざるを得ません。
>>雛形が大きな表になってしまい、まして、日々、シートを印刷までしているの
>>で、ここも頭が痛いんです
>この場合「データ未入力の行を一気に非表示...」というマクロを組むだけです。
>例えば B列を基準とするなら
>
>Sub R_Hidden_Change()
>  Static Hck As Boolean
> 
>  On Error Resume Next
>  If Hck = False Then
>   If WorksheetFunction.CountA(Range("B:B")) = 0 Then
>     MsgBox "B列に値がありません", 48: Exit Sub
>   End If
>   Range("B1", Range("B65536").End(xlUp)) _
>   .SpecialCells(4).EntireRow.Hidden = True
>   Hck = True
>  Else
>   Cells.EntireRow.Hidden = False
>   Hck = False
>  End If
>End Sub
>
そうですね!、近頃どうも頭が固くなって来てるなーー...いかん!いかん!
ありがとうございます。
>シートを丸ごとコピーすると、シートモジュールのコードは付いてくる筈です。
>こちらでもテストで確認しています。標準モジュールについては無理ですから、
>定数 Mdl に指定します。するとコードによってActiveWorkbookにインポートされ>ます。
これは解決しました。 でも、1つ疑問があります。
と言うのは、作成されたBook(9月.xls)を開いて、VBEでシート名をダブルクリックすると、原紙(非表示にされています)のシートモジュールは、コードが表示されますが、今回のマクロで作成した日付のシートをダブルクリックしてもコードが表示されません、それで、コピーされていないと思っていたのですが、今回標準モジュールをMdlで取り込んだら、何と、シートモジュールがない日付のシートでも動作するのです!、これはどういう事でしょうか?
>>今回の各シートはセル幅、高さともコピーされていません。
>これはこちらでも確認しました。UsedRangeでコピーしていたためのようです。
>Cells全体をコピーするコードに変えた結果、行高・列幅も反映されています。
これもバッチリでした!(CellsとUsedRangeの違いも勉強になりました)

さて、何とか Kein さんからごご提示頂いた当月一ヶ月分の、新しいブックを作成するマクロは完成しました。
これを元に、日々回していく運用のマクロを考えてみます。
(まだまだ、先は遠いなーー)
チョットしたヒントをご提示して頂けたら幸いです。
よろしくお願いします。m(_ _)m
>
>Sub ThisMonth_Make_NewBook2()
>  Dim MkFile As String
>  Dim Ans As Integer, Scnt As Integer, NewS As Integer
>  Dim SDay As Date
>  Dim WS As Worksheet
>  Const Mdl As String = _
>  "C:\Documents and Settings\User\My Documents\モジュール\Module1.bas"
> 
>  MkFile = Application.DefaultFilePath & _
>  "\" & Month(Date) & "月.xls"
>  If Dir(MkFile) <> "" Then
>   Ans = MsgBox("今月のブックは既に存在します" & vbLf & _
>   "削除して新規にブックを作成しますか", 36)
>   If Ans = 7 Then Exit Sub
>  End If
>  NewS = Day(DateSerial(Year(Date), Month(Date) + 1, 1) - 1)
>  SDay = DateSerial(Year(Date), Month(Date), 1)
>  With Application
>   Scnt = .SheetsInNewWorkbook
>   .SheetsInNewWorkbook = NewS
>   .ScreenUpdating = False
>  End With
>  Workbooks.Add
>  With ActiveWorkbook
>   For Each WS In .Worksheets
>     WS.Name = CStr(Format(SDay, "m月d日"))
>     SDay = SDay + 1
>   Next
>   ThisWorkbook.Sheets("test").Copy Before:=.Worksheets(1)
>   .Sheets.FillAcrossSheets .Sheets("test").Cells
>   .Sheets("test").Visible = False
>   .VBProject.VBComponents.Import Mdl
>   .SaveAs MkFile
>   '.Close
>  End With
>  With Application
>   .ScreenUpdating = True
>   .SheetsInNewWorkbook = Scnt
>  End With
>End Sub

【18256】Re:行数が不定の集計処理について
回答  Kein  - 04/9/19(日) 20:11 -

引用なし
パスワード
   余計なことですが
>事務所から12〜3Km
も離れていて、毎朝そこまで出かけてFDに落として、事務所へ持ち帰るのですか ?
ふぅ・・それはご苦労なことです。LANは無理にしても、インターネットに接続する
ぐらいは、上司に交渉した方が良さそうですね。メール添付でもYahooブリーフケース
でも、居ながらにしてデータを取得できるわけですから。
さて
>今回のマクロで作成した日付のシートをダブルクリックしてもコードが表示されません
当然ですね。新規シートとして挿入されているだけですから。つまり原紙シートのみを
元のブックからコピーしているのです。こちらには元ブックの現状が、さっぱり
わかりませんから、白紙状態から作り直すことだけを考えているわけです。元ブック
の状態が詳しく説明されれば、何をコピーして何を新規作成すれば良いか、ある程度の
見当がつくはずなんですが・・。だから
>今回標準モジュールをMdlで取り込んだら、何と、シートモジュールがない日付の
>シートでも動作するのです
と言われても、標準モジュールにどんなマクロがあり、日付のシートにはどんな
マクロがあったのかも知らないので、何が起きているか ?チンプンカンプン? です。
とりあえず今、回答できることと言えば「エクスポートによって保存したモジュール
ファイルは、VBProject.VBComponents.Import というコードでインポートできますよ」
ということぐらいです。コードの意味を理解していれば、簡単な応用(追加するだけ)
で出来ると思いますが・・。

【18257】Re:行数が不定の集計処理について
回答  ackkn  - 04/9/19(日) 23:22 -

引用なし
パスワード
   ▼Kein さん:
Kein さん、こんばんは。
>毎朝そこまで出かけてFDに落として、事務所へ持ち帰るのですか ?
いえいえ、作業者が毎朝外部倉庫へ行き、入庫作業を行い、その結果(日付のシート)を夕刻に持ち帰るのです。 私は、そのデータを貰って最終のデータ修正をする訳です。(データ修正とは、在庫管理のロケーション確定(修正)作業です)
>と言われても、標準モジュールにどんなマクロがあり、日付のシートにはどんな
>マクロがあったのかも知らないので、何が起きているか ?チンプンカンプン?
申し訳ありません。m(_ _)m
ある程度の作業内容を、
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=18189;id=excel
の時に書いたのと、最初の質問の際に日付のシートの内容も書いていたので解って貰えていると勝手に思い込んでいました。
要は、倉庫の在庫管理なんですけど、詳しく説明しますと、日々リンゴの色々な品種が入荷します。 で、それらを倉庫へ格納する訳ですが、当然入荷した物(りんご)はいずれ出荷されますから、格納する際には倉庫内のロケーションを紐付けて格納します。(例えば、ふじ(品種)は2階のAエリアの15列目とか)それらが記入されたシートが日付シートです。 だから、最初の質問の時の下図の右側(F列以降)には書いていませんでしたがロケーションデータが入っています。
> A列  B列   C列  D列  E列
> チェック 品種  数量 ケース数 入数
>    つがる 280  3   60
>    ふじ  570  8   80
>    スター 480  12   40
>    合 計 1330  23 ← この行を出す
で、私が居る倉庫(メイン倉庫とします)だけでは、今の時期キャパが足りない為に今回の外部倉庫が登場している訳です。
メイン倉庫内への格納データは、即時に確定、更新されますが、外部倉庫の場合は、上記のように夕刻にシートが戻って来る迄は、外部倉庫というロケーションで仮設定しておき、日付シートが戻った時点で先程書きましたように、私が正式なロケーションに確定(訂正)する訳です。
これが、日付シートの全貌です。

それと、そのシートモジュールも、この質問箱で解決して頂いた物ですが、Kein さんにとっては未知でした。
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=18157;id=excel
シートモジュールは、上記リンクのmigiとHidariのみです。
これは、上記ロケーションを入力するエリアで使用している物で、ロケーションの表記(外部倉庫の2階のAエリアの15列目→G(外部の略)-2-A-15と入力)イメージで入力する為のカーソル支援です。
標準モジュールは、上記のmigi、hidariの連れの他は、今回のKein さんからの集計処理とシート作成(Sub ThisMonth_Make_NewBook())のみです(今のところ)、あっ!、それと途中Kein さんに質問して作りかけの値クリア(もう不要)があります。 この日付シートの未記入版が、私が原紙と言っているシートの事です。

これらが全貌です、Kein さん解って頂けたでしょうか?

【18259】Re:行数が不定の集計処理について
回答  Kein  - 04/9/20(月) 0:56 -

引用なし
パスワード
   なるほど、それで概ね分かりました。ま、あなたの仕事の内容をお尋ねしたわけでは
ありませんから、それはともかくとして、各モジュールに存在するマクロのコード
さえ分かれば、設計の見直しは出来ます。
まず、日付シートのイベントマクロがコピーされていないのに、なぜか migi,hidari
のマクロが動作した。という理由ですが、結論から言うと "Right,Leftキーに
登録したマクロが解除されていないから" です。これはキーを開放するイベントに
Worksheet_Deactivate を使ったことによって "ブックを閉じる前に、一度他の
シート(イベントマクロを仕込んでいない)を開くことなくそのまま閉じた" ため、
イベントが発生しないままになっているわけです。無意識で閉じてしまえば、当然
起こり得る結果です。なのでイベントの種類の選定を、間違えていると言えます。
(回答者の方は何も説明されないままに、コードを組まれたのでしょうから、回答を
間違いと言ってるわけではありません。念のため。)
で、くどくど説明を続けるより、対処の仕方を指南します。
まず、原紙シートに Worksheet_Activate, Deactivate のマクロがあれば、それを
削除して下さい。そして標準モジュールに以下のマクロを入れて下さい。

Private Hck As Boolean 'モジュールの先頭に宣言

Sub Auto_Open()
  Dim x As Integer

 With Application
   .OnKey "{RIGHT}", "migi"
   .OnKey "{LEFT}", "hidari"
   .OnDoubleClick = "R_Hidden_Change"
  End With
  ThisWorkbook.OnSheetActivate = "Flg_Off"
  x = Day(Date) - 1
  Worksheets(x).Activate  
End Sub

Sub Auto_Close()
  With Application
   .OnKey "{RIGHT}"
   .OnKey "{LEFT}"
   .OnDoubleClick = ""
  End With
  ThisWorkbook.OnSheetActivate = ""
End Sub

Sub migi()
  With ActiveCell
   If .Column > 254 Then Exit Sub
   If .Offset(, 1).Value = "-" Then
     .Offset(, 2).Select
   Else
     .Offset(, 1).Select
   End If
  End With
End Sub

Sub hidari()
  With ActiveCell
   If .Column < 3 Then Exit Sub
   If .Offset(, -1).Value = "-" Then
     .Offset(, -2).Select
   Else
     .Offset(, -1).Select
   End If
  End With
End Sub

Sub Flg_Off()
  Cells.EntireRow.Hidden = False
  Range("A1").Select
  Hck = False
End Sub

Sub R_Hidden_Change()
  If ActiveCell.Row > 1 Then Exit Sub
  On Error Resume Next
  If Hck = False Then
   If WorksheetFunction.CountA(Range("B:B")) = 0 Then
     MsgBox "B列に値がありません", 48: Exit Sub
   End If
   Range("B1", Range("B65536").End(xlUp)) _
   .SpecialCells(4).EntireRow.Hidden = True
   Hck = True
  Else
   Cells.EntireRow.Hidden = False
   Hck = False
  End If
End Sub

標準モジュールは、上記のマクロだけあればいいです。
>Kein さんからの集計処理
は先にレスしたように、原紙シートで一定のセル範囲に対して表を作り、その最終行
に合計の数式を入れておく、という仕様にすることで不要になるばです。いちいち
最終入力行を求めて、その一行下に数式を入れて・・という形にするより合理的、
ということが理解されたと思うからです。
>シート作成(Sub ThisMonth_Make_NewBook())
これはいちおう、月毎のブック"以外"から作成することを前提にしていたので、
月毎のブックそのものに入れてしまうのは、ちょっと違和感がありますけどね・・。
ま、その方が便利と思うなら、入れておいても構いません。

とにかく今回は、ご覧のように新しいマクロも含めて提示してみましたが、簡単に
説明すると「本来シートモジュールやThisWorkbookモジュールに入れるイベント
マクロを、全て標準モジュール用に書き直し、インポートファイルが一つで済む
ようにした」ということです。
migi,hidari のマクロは、ちょっと手直ししただけです。
Auto_Open, Auto_Close は、ブックを開いたとき、閉じたときに発生するイベント
です。ここにキーへのマクロ割り当てと、その開放をする OnKey のコードを持って
きたことによって、ブックを開いてすぐに、どのシートでもキーアクションを使え、
閉じると自然に開放されるようになります。あと、空白行の表示・非表示については
どのシートでも一行目(項目があると仮定した)の任意のセルをダブルクリックする
ことで、起動するようにしました。それで使い勝手が良くなっていると思います。
あとOnSheetActivate は、任意のシートを開いたときの初期設定をしているだけです。
なお、これらのイベントについては OnKey を除いて、今どきのExcelではヘルプに
出てきません。なぜならそれは Excel95以前に使われていたものだからですが、
決して機能が劣るから使われなくなったのではありません。むしろコーディングの
柔軟性は、現在のイベントマクロより優れていると思われます。今回のように適材適所
の使い方をすれば、まだまだ活用できると考えています。

もう一度、上記の標準モジュールをエクスポートし、ブックを作り直してみて下さい。

【18263】Re:行数が不定の集計処理について
お礼  ackkn  - 04/9/20(月) 6:38 -

引用なし
パスワード
   ▼Kein さん:
Kein さん、おはようございます。
レス、ありがとうございます。
解って頂けたようで、ホッとしました。(^o^)
まずは御礼の一言と思い書きました。
早速試してみたいと思います。

【18289】Re:行数が不定の集計処理について
質問  ackkn  - 04/9/20(月) 17:58 -

引用なし
パスワード
   ▼Kein さん:
>削除して下さい。そして標準モジュールに以下のマクロを入れて下さい。
シートモジュールを削除して、標準モジュールにご提示して頂いたコードを入れて実行しました。 シートが原紙だけだった為に、
  x = Day(Date) - 1
  Worksheets(x).Activate
の部分でいきなり引っかかりましたが、そこはパスさせて起動しました。
(強引になので、実際の運用時(日々シート作成)には変更が必要です)
その後、カーソル移動は問題なく従来通りに動いてくれました。
Sub hidari()
  With ActiveCell
   If .Column < 3 Then Exit Sub
ただ、A列にカーソルが移動しなかったので、上記の3を2に変更してOKになりました。
問題は、ダブルクリックのデータなし行の非表示がうまくいきません。
と言うのは、実際の表が2行目までタイトルで、3行目が項目名で、実際のデータは4行目から入っています、この状態で正常に動作する修正箇所を教えて下さい。

【18293】Re:行数が不定の集計処理について
回答  Kein  - 04/9/20(月) 22:18 -

引用なし
パスワード
   まず、シートを開くコードの修正ですが

 If Worksheets.Count > 28 Then
   x = Day(Date) - 1
   Worksheets(x).Activate
 End If  
End Sub

というようにしておけば、最小枚数になる2月でもOKでしょう。
>実際の表が2行目までタイトルで、3行目が項目名で、実際のデータは4行目
それは問題でないと思います。ただ1・2行目をセル結合していると、どんな動作に
なるか見当がつかないので、クリックする行を「3行目」に固定するとにします。
それから、データの有無をチェックする列は「B列」で良いのですか ?
他の列の方が良いなら、適当にそちらで変更して下さい。いちおう・・

Sub R_Hidden_Change()
  If ActiveCell.Row <> 3 Then Exit Sub
  'On Error Resume Next
  If Hck = False Then
   If WorksheetFunction.CountA(Range("B4:B65536")) = 0 Then
     MsgBox "B列に値がありません", 48: Exit Sub
   End If
   Range("B4", Range("B65536").End(xlUp)) _
   .SpecialCells(4).EntireRow.Hidden = True
   Hck = True
  Else
   Cells.EntireRow.Hidden = False
   Hck = False
  End If
End Sub

というように修正して下さい。On Error Resume Next をコメント化しているので
エラーか出たらデバッグするはすです。出たら内容を報告して下さい。
あと、モジュールの先頭に

Private Hck As Boolean

の宣言はしてありますか ?

【18299】Re:行数が不定の集計処理について
質問  ackkn  - 04/9/20(月) 23:42 -

引用なし
パスワード
   ▼Kein さん:
Kein さん、こんばんは。
>クリックする行を「3行目」に固定するとにします。
>それから、データの有無をチェックする列は「B列」で良いのですか ?
>他の列の方が良いなら、適当にそちらで変更して下さい。いちおう・・
>
>Sub R_Hidden_Change()
>  If ActiveCell.Row <> 3 Then Exit Sub
>  'On Error Resume Next
>  If Hck = False Then
>   If WorksheetFunction.CountA(Range("B4:B65536")) = 0 Then
>     MsgBox "B列に値がありません", 48: Exit Sub
>   End If
>   Range("B4", Range("B65536").End(xlUp)) _
>   .SpecialCells(4).EntireRow.Hidden = True
>   Hck = True
>  Else
>   Cells.EntireRow.Hidden = False
>   Hck = False
>  End If
>End Sub
>
なりました、なりました(^_^)
ありがとうございました。
もう一つ教えて下さい。
この縮めた状態の時は、集計行含めたエリアを印刷範囲に設定し、解除したら印刷範囲もクリアするようには、どうしたら出来ますか?
よろしくお願いします。

それと、チョット高度すぎるので、簡単な解説を入れてもらえませんか?
1つづつ自分の物にしていきたいので、よろしくお願いします。m(_ _)m

【18301】Re:行数が不定の集計処理について
回答  Kein  - 04/9/21(火) 2:13 -

引用なし
パスワード
   コードの意味ですが

Sub R_Hidden_Change()
  If ActiveCell.Row <> 3 Then Exit Sub
  'もしアクティブセル(ダブルクリックしたセル)の行が 3 以外なら中止

  'On Error Resume Next
  'これから下でエラーが出たら、それをスキップする

  If Hck = False Then
  'モジュールレベル変数の値が False なら

   If WorksheetFunction.CountA(Range("B4:B65536")) = 0 Then
   'もし B4以下のセルに値が入力されていなかったら

     MsgBox "B列に値がありません", 48: Exit Sub
    'メッセージを出して中止

   End If
   Range("B4", Range("B65536").End(xlUp)) _
   .SpecialCells(4).EntireRow.Hidden = True
   'B4〜入力最終行までで、空白セル(SpecialCells(4))の行範囲を非表示に
   'する。SpecialCellsメソッドは該当するセルが見つからないとエラーに
   'なり、それを事前に検知・回避することが出来ないので、先に On Error 〜
   'を入れておいた。

  Hck = True
  '変数の値を True に変更

  Else
  '変数の値が True なら

   Cells.EntireRow.Hidden = False
   'セル全体の行範囲を表示する

   Hck = False
   '変数の値を False に変える
  End If
End Sub

ということになっています。
>集計行含めたエリア
は、例えば B列を基準に最終行を判定し、F列までが表の範囲とするなら

Range("B1", Range("B65536").End(xlUp)).Offset(, -1).Resize(, 5)

になります。ですからここのアドレスを PrintArea プロパティの値に渡してやれば
良さそうなんですが、ちょっとこちらでテストしてみましたが、イマイチうまく
印刷範囲を決定できませんでした。なので印刷時には、それ専用のマクロも入れて
おく、ということで確実な処理をさせるようにします。印刷専用マクロは

Sub MySheet_Print()
  Dim PArea As Range
  Dim Sh As Worksheet
  Dim Ans As Integer

  If Hck = False Then Exit Sub
  Set PArea = Range("B1", Range("B65536").End(xlUp)) _
  .Offset(, -1).Resize(, 5).SpecialCells(12)
  On Error Resume Next
  Set Sh = Worksheets("MyPrint")
  If Err.Number > 0 Then
   Set Sh = Worksheets _
   .Add(After:=Worksheets(Worksheets.Count)).Name = "MyPrint"
   Err.Clear
  End If
  Sh.Activete: Cells.Clear
  PArea.Copy Sh.Range("A1")
  Set PArea = Nothing: Set Sh = Nothing
  Ans = MsgBox("印刷を開始しますか", 36)
  If Ans = 6 Then ActiveSheet.PrintOut Copies:=1
End Sub

というコードでよいでしょう。
あと、この印刷マクロを実行するのに、いちいち「ツール」「マクロ」・・を選択
するのが面倒なら、ダブルクリックイベントで呼び出すマクロで、ダブルクリックした
セルのアドレスを判定し

" A3 セルなら行の表示・非表示切り替え、E3 セルなら印刷マクロを呼び出す"

という形に改造しておくと良いと思います。その場合は R_Hidden_Change を

Sub R_Hidden_Change()
  Select Case ActiveCell.Address
   Case "$A$3"
     GoTo RoLine
   Case "$E$3"
     Call MySheet_Print
  End Select
  Exit Sub
RoLine:
 On Error Resume Next
  If Hck = False Then
   If WorksheetFunction.CountA(Range("B4:B65536")) = 0 Then
     MsgBox "B列に値がありません", 48: Exit Sub
   End If
   Range("B4", Range("B65536").End(xlUp)) _
   .SpecialCells(4).EntireRow.Hidden = True
   Hck = True
  Else
   Cells.EntireRow.Hidden = False
   Hck = False
  End If
End Sub

というように変更して下さい。

11074 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free