エクセルの自動更新のタイミングについて

エクセル関数が指定のセルを読みに行くタイミングとイベントプロシージャでセルが変更した時にマクロを呼びだし、呼び出されたマクロが実行されるタイミングがずれているので困っています。
例
セルL6のエクセル関数　=IF(L40=0,"休","")
セルL7のエクセル関数　=IF(L40=0,"休","")
セルL8のエクセル関数　=IF(L40=0,"休","")
セルL9のエクセル関数　=IF(L40=0,"休","")

セルL35のエクセル関数　=2-COUNTIF(L6:L9,"出")

イベントプロシージャ
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target,Range("L6:L9")) Is Nothing Then
Call コピー
End If
End Sub

Sub コピー()
Worksheets(1).Select
Range("L35").Copy
Range("L40").PasteSpacial Paste:=xlPasteValues
End Sub

簡単な例ですが、セルL6～L9のセルに出を2回入力した時、残りのセルに休を出力します。

この時、L35とL40の値が同じにならない時があり、その時に期待された結果が表示されません。
どうすれば、L35とL40の値が常に一致させることができるか分かりません。

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

• 返信ありがとうございます。
  実は勤務表のシフト作成補助を自動で行おうとしたものです。
  実際の作成したコードはもっとずっと長いもので、カレンダーの表にシフトをはめ込んでいくことを意図した実験的な試作品です。実際は休、出ではなくシフト記号、希望休など様々なものがあり、条件がある程度整ったセルにシフトを割り当てるものです。
  一日分のシフトが出来たと思い別のセルを変更すると、作成済のセルが再び変わってしまい崩れてしまうことに困っています。勿論何度か修正すれば、正しくなりますが。エクセル関数の入ったセルは、マクロで式を入力するようになっているので上書きしても大丈夫です。

  No.1の回答に寄せられた補足コメントです。 補足日時：2022/07/20 17:24

No.1 ベストアンサー 回答者： fujillin 回答日時： 2022/07/20 17:02

こんにちは

何を意図している仕組みなのかよくわかりません。

＞セルL6～L9のセルに出を2回入力した時～
L6:L9には関数が設定されているのに、それを上書きして文字を入力する仕組みなのでしょうか？
であるなら、それ以降変更しても関数の影響はなくなります。
（通常、あまりこのような仕組みにはしないと思いますが・・）

＞どうすれば、L35とL40の値が常に一致させることができるか分かりません。
L40は自由入力ができますので、極端な話として、L6に「出」を入力後、L40に「0」を入れれば、L35とL40の値は違うものになります。

一致させておきたいのなら、L40に ＝L35 の式を入力して保護をかけておけば、常に一致します。
（シートが「自動計算」になっていることが条件ですが）

L6:L9に「出」以外の値（＝空白等）を残したくない、と言う意図ならば、Changeイベントの処理で「出」以外のものを、強制的に「休」に書き換えてしまえばよいのでは？
（初期表示は、直接「休」の文字を入力しておく）


「何をどうしたいのか」をもう一度整理したうえで、仕組みを考えた方がよさそうな気がします。

この回答へのお礼

ありがとうございます
もともとエクセル関数のみで、式を見に行って循環エラーとなりやむなくいろいろ小技を使ったのですが・・・。
もう一度設計自体を考えてみます。

お礼日時：2022/07/20 17:30