Excel VBAについて。こんな動作をさせるためにはどう書けばよいでしょうか。

添付のような表があります。（実際は100行くらいあります）
例えばAさんは1/13,14,15と滞在する予定になっていますが、
日ごとに何人が滞在しているかカウントするマクロが作りたいです。
ボタンを押すと、1/13は何人、1/14は何人、、と結果が出てくるのが理想です。
また、（これはできればなのですが）BさんのようにD列に「前泊」という文字がある人については出発日の翌日から滞在としたいです。例えばBさんは1/15~17で滞在ということになります。

マクロ初心者なのですが調べようにもなんて調べたらいいのかも分からず、得意な方がいらっしゃれば教えていただきたいですm(__)m
ボタンの作り方や変数の定義など基本的な部分はネットで調べて分かるようになりました

質問者からの補足コメント

• １．レイアウトは何でもいいと思っています。列が二つあって1列目が日付（今回でいうと1/13,1/14・・・）2列目が人数という表ができあがればよいかなと思っています。
  ２.滞在予定の表があるシートが「滞在予定」人数をカウントするシートが「集計結果」としようと思っています。
  ３.必ず設定されています
  よろしくお願い致します・・・！

  No.1の回答に寄せられた補足コメントです。 補足日時：2024/12/26 19:59

• 

  ありがとうございます、
  すみません、実際に実行してみて質問なのですが、
  ↓の部分で「日付不正」と出てきてしまいます。表の位置など変えておりません。考えられる対策はありますでしょうか
  '出発日チェック
  If IsDate(ws1.Cells(row1, "E").Value) = False Then
  Call err_proc(ws1, ws1.Cells(row1, "E"), "日付不正")
  End If
  '帰宅日チェック
  If IsDate(ws1.Cells(row1, "F").Value) = False Then
  Call err_proc(ws1, ws1.Cells(row1, "F"), "日付不正")
  End If

    補足日時：2024/12/27 11:13

• 

  親切な回答ありがとうございます。教えていただいた内容で１つ１つ解決してます
  実際のファイルで実行したところ、↓でエラーが発生しました
  （start_date '集計開始日）
  （end_date '集計終了日）
  （sdate '滞在者の滞在開始日）
  （edate '滞在者の滞在終了日）
  For wdate = sdate To EDate
  '集計結果の行番号を算出する
  row2 = wdate - start_date + 2
  '算出した行に１加算する
  ws2.Cells(row2, "B").Value = ws2.Cells(row2, "B").Value + 1
  考えたところ、実際のファイルには過去データが存在することが原因かなと思いました。（滞在開始日＜集計開始日の行あり）
  予め伝えずに質問したのが悪いのですが、解決するのは結構おおがかりになりそうでしょうか

    補足日時：2024/12/28 22:31

• 

  アプリケーション定義またはオブジェクト定義のエラーです、というメッセージが出て
  ws2.Cells(row2, "B").Value = ws2.Cells(row2, "B").Value + 1
  ここで止まりました。
  sdateに過去日付が入り、start_dateが1/13なのでrow2がマイナスの値になっていたことが気になりました。
  関係あるか分からないのですが出発日が未記入の行も存在したためFor分の前にIf IsDate(sdate) Thenを追加しました(最初にsdateの型の定義をしているとIsDateがすべてTRUEになってしまうと思ったので最初のDim~みたいなのはコメントアウトしました）

  No.7の回答に寄せられた補足コメントです。 補足日時：2024/12/29 00:05

• すみません、連続の投稿になります。
  画像のようなボタンを作って↓のように集計開始日をセットしたのが原因でした。。
  start_date = ws1.Cells(1, "AG").Value
  end_date = ws1.Cells(2, "AG").Value

  
    補足日時：2024/12/29 00:13

• 当初基本的な動きだけ教えてもらい、あとの細かい仕様は自分で考えようと思っていたのですが
  全然うまくいきませんでした。
  結局また仕様の確認をしていただくことになってしまいすみませんm(__)m
  １．①～④、まですべてOKになります。
  >②のケースの場合の人数カウントの方法ですが、どのようにしたいのでしょうか。
  →➁～④のすべてのケースの場合は処理をスキップしたいです。（前泊という文字があっても0人としてよい）
  ２．記載いただいたカウント方法で合っています。
  ３．C列の名前が入っている行を最終行にしたいです。
  ４．歯抜けのある行はエラーになる仕様で問題ありません。
  関係ないかもしれませんが、先ほど貼った画像のようなボタンはws1に作成しました。

  No.8の回答に寄せられた補足コメントです。 補足日時：2024/12/29 09:55

No.10 ベストアンサー 回答者： tatsumaru77 回答日時： 2024/12/31 12:49

No9です。

No9は、急いで作ったので、整理されていませんでした。
整理しなおしたものを、下記のURLにアップしました。
要件を見直すと、滞在予定表を2度スキャンする必要がなくなりましたので、
１度のスキャンで終わるようにしました。
又、エラー発生時のメッセージを、エラー箇所がわかりやすくなるようにしました。集計結果は、No9と同じ内容になります。

https://ideone.com/RKWY0C

この回答へのお礼

完璧にできました
とても助かりましたし私自身とてもお勉強になりました。
ありがとうございましたm(__)m

お礼日時：2024/12/31 20:06

No.9 回答者： tatsumaru77 回答日時： 2024/12/29 11:15

下記へアップしました。

https://ideone.com/cXPq72

No.8 回答者： tatsumaru77 回答日時： 2024/12/29 09:33

ほぼ、状況はわかりました。

当初と仕様が変わってますので、仕様の確認から行います。

１．出発日と帰宅日です。
出発日　帰宅日
記入　　記入　　・・・①
未記入　記入　　・・・②
記入　　未記入　・・・③
未記入　未記入　・・・④

当初は①のみOKで、ほかのケースはNGということでしたが、
今回②のケースもOKとなりました。
③、④のケースは、どうなりますか。OKでしょうか、NGでしょうか。

②のケースの場合の人数カウントの方法ですが、どのようにしたいのでしょうか。
集計開始日　2025/1/1
集計終了日　2025/1/5
でAさんが（Aさんのみとします）
出発日　未記入
帰宅日　2015/1/2
のとき、
日付　　　　人数
2025/1/1　　1
2025/1/2　　1
2025/1/3　　0
2025/1/4　　0
2025/1/5　　0
となれば、よいのでしょうか。（D列は前泊でも、空白でも同じ結果となる）
もし、そうでないなら、例を挙げて示してください。

③、④のケースをOKとしたい場合は、人数のカウントの方法を②のケースのように提示してください。

２．集計開始日と集計終了日です。
以下のような集計開始日～集計終了日の範囲外に出発日と帰宅日があるケースの人数のカウント方法です。
集計開始日　2025/1/1
集計終了日　2025/1/5
の時、
氏名　前泊か否か　出発日　　　　　帰宅日
A　　前泊　　　　2024/12/30　　2024/12/31・・・①
B　　　　　　　　2025/1/6　　　2025/1/7・・・・②
C　　前泊　　　　2025/1/4　　　2025/1/7・・・・③
D　　　　　　　　2024/12/30　　2025/1/1・・・・④

①のケースは、2025/1/1～2025/1/5の間、人数カウントしない。
②のケースは、2025/1/1～2025/1/5の間、人数カウントしない。
③のケースは、2025/1/1～2025/1/4の間、人数カウントしない。
　2025/1/5のみ１名分カウントする。
④のケースは、2024/12/30～2024/12/31の間、人数カウントしない。
　2025/1/1のみ１名分カウントする。

上記のようなカウント方法で良いでしょうか。

３．最終行数を決定する列について
現在は、B列のNoが記入されている行を最終行にしています。
これで、よいのでしょうか。
それとも、C列の氏名が記入されている行を最終行にしたいのでしょうか。

４．歯抜けのある行について
現在は、歯抜けのある行は、エラーにしています。
この仕様で問題ないでしょうか。それとも、歯抜けのある行は、スキップして処理するようにしたいのでしょうか。その場合、歯抜けのある行についての定義を再確認する必要があります。

上記について、補足をお願いします。又、上記外に仕様が変わっているとこがあれば、それも提示してください。

No.7 回答者： tatsumaru77 回答日時： 2024/12/28 23:41

>実際のファイルで実行したところ、↓でエラーが発生しました

>（start_date '集計開始日）
>（end_date '集計終了日）
>（sdate '滞在者の滞在開始日）
>（edate '滞在者の滞在終了日）
>For wdate = sdate To EDate
>'集計結果の行番号を算出する
>row2 = wdate - start_date + 2
>'算出した行に１加算する
>ws2.Cells(row2, "B").Value = ws2.Cells(row2, "B").Value + 1
>考えたところ、実際のファイルには過去データが存在することが原因かなと>思いました。（滞在開始日＜集計開始日の行あり）
>予め伝えずに質問したのが悪いのですが、解決するのは結構おおがかりにな>りそうでしょうか

エラー発生時の、エラーメッセージの内容を提示してください。
エラー発生時、デバッグ状態になり、マクロのどこかの行で停止しますが、
上記のどの行で停止していますか。（停止した行は黄色になります）


滞在開始日＜集計開始日の行あり・・・とのことですが、
滞在開始日の最も小さい値（最も古い日付）を集計開始日
にしているので、この事象は発生しないはずですが、滞在予定シートにはどのような記入をされているのでしょうか。

No.6 回答者： tatsumaru77 回答日時： 2024/12/27 12:04

No4です。

添付図のように、行の歯抜けがあると、エラーが発生します。
７行目が歯抜けの行なので、その場合、E7のセルの日付が不正な日付になります。行の歯抜けは想定していません。もし、歯抜けが存在するようでしたら、その旨、補足してください。

No.5 回答者： tatsumaru77 回答日時： 2024/12/27 11:38

No4です。

「滞在予定」シートの最終行は、B列の最終行で判断しています。
B列の添付図のようになっていると、9行目が最終行と判断します。
そのため、E9のセルは、空白なので日付不正になります。
もし、このような状態であれば、以下の対策を施してください。
対策案１又は対策案２の何れかを実施すればOKです。

対策案１：
８行目が実質的な最終行なので、B列の９行目以降を空白にする。

対策案２：
C列（氏名）で最終行の判断をする。
その場合は、
lastRow = ws1.Cells(Rows.count, "B").End(xlUp).Row 'B列最終行取得
を
lastRow = ws1.Cells(Rows.count, "C").End(xlUp).Row 'C列最終行取得
に変えてください。

No.4 回答者： tatsumaru77 回答日時： 2024/12/27 11:23

>↓の部分で「日付不正」と出てきてしまいます。

表の位置など変えておりません。考えられる対策はありますでしょうか

この日付不正のメッセージが表示されると、その不正なセルを選択して、マクロが止まります。
その選択されたセルの内容は、どうなっていますか？
そのセルの内容が日付としておかしい場合に、エラーが発生します。
その選択されたセルには、何が表示されていますでしょうか。

No.3 回答者： fujillin 回答日時： 2024/12/26 22:25

こんばんは

マクロは多分No1様が作成してくれるでしょうから、別の方法での例です。

スピル機能が使えるバージョンでの例ですが・・
添付図では、I3セルに
=MAP(H3:H99,LAMBDA(d,IF(d="","",SUMPRODUCT(((E3:E99+(D3:D99="前泊"))<=d)*(d<=F3:F99)))))
の関数式を入力してあります。

これによって、Ｈ列に任意の日付を入力すると、その日付に応じた滞在人数がＩ列に表示されるようになります。

※ マクロの場合は、毎回計算を実行させる必要がありますが、関数の
　 場合は即時に反映してくれます。
※ シート内の「日付」は全てシリアル値（＝エクセルの日付型の数値）
　 であるものと仮定しています。

ご参考にでもなれば。

No.2 回答者： tatsumaru77 回答日時： 2024/12/26 21:24

下記にアップしました。

標準モジュールに登録してください。
https://ideone.com/CR1YW2

「滞在予定」シートは提示されたレイアウト通りであることが前提です。
空の「集計結果」のシートをあらかじめ作成しておいてください。
集計結果のA列に日付、B列に人数を出力します。
「滞在予定」シートのD列は、空白か前泊となります。（前泊以外の文字があるとエラーとなります）

No.1 回答者： tatsumaru77 回答日時： 2024/12/26 19:38

１．日ごとに何人が滞在しているかをどのような表に出力したいのでしょうか。

そのレイアウトを提示していただけませんでしょうか。

２．日ごとに何人が滞在しているかを出力するシートと、客の滞在予定の表が、異なるシートなら、それぞれのシート名も提示してください。

３．出発日と帰宅日は、ともに日付が必ず設定されてますでしょうか。
（どちらかの日付が空欄のようなケースはない。）