計算によってセルの値に変化があった時にマクロを実行する

質問

計算によってセルの値に変化があった時にマクロを実行する

"食品一覧"シートのＶ列の値が変わったら、データを“フィルタオプションの設定”でフィルタにかけ、特定の項目を同じブックの別のシートに抽出するマクロ文を以下の通り作成しました。

Private Sub Worksheet_Change(ByVal Target As Range)

With Worksheets("食品一覧")

If Intersect(Target, Range("V5:V10000")) Is Nothing Then Exit Sub
Application.EnableEvents = False

Worksheets("賞味期限切れ").Range("A7:Z10000").Clear
Worksheets("その他").Range("A7:Z10000").Clear

.Range("A4:Z1000").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("賞味期限切れ").Range("A1:A2"), _
CopyToRange:=Worksheets("賞味期限切れ").Range("A6:G6"), _
Unique:=False

.Range("A4:Z1000").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("その他").Range("A1:A2"), _
CopyToRange:=Worksheets("その他").Range("A6:G6"), _
Unique:=False

Application.EnableEvents = True

End With

End Sub

上記マクロ文は、Ｖ列の値を手入力で変更した場合のみ有効です。

やりたい事は、「Ｖ列に数式を入力し、計算によってＶ列の値に変化があった時にマクロを実行する」です。
↓入力する数式は以下の数式です。
=IF(DATE(YEAR(A10),MONTH(A10),DAY(A10))<$A$1,"期限切れ","その他")

色々調べた結果、

手入力によりセルの値が変更される場合は“Change”
Private Sub Worksheet_Change()

数式によりセルの値が変更になる場合は“Calculate”
Private Sub Worksheet_Calculate()

(※解釈を違えていたらすみません、間違えていたらそれぞれの用途を教えて下さい。）

と言う事なので、今回はPrivate Sub Worksheet_Calculate()を使用してマクロ文を作成したいのですが、上手く作成する事ができません。

お解かりになる方、ぜひ教えて下さい。

回答No.1

keithin · ベストアンサー

方法１：
ご相談の意図に沿ったものを作ると，次のようになります。

Private Sub Worksheet_Change(ByVal Target As Range)

　With Worksheets("食品一覧")
　’この行は使えないので削除する
　’If Intersect(Target, Range("V5:V10000")) Is Nothing Then Exit Sub
　’以下は今までのまま使う

　Application.EnableEvents = False
　Worksheets("賞味期限切れ").Range("A7:Z10000").Clear
　Worksheets("その他").Range("A7:Z10000").Clear

　.Range("A4:Z1000").AdvancedFilter _
　　Action:=xlFilterCopy, _
　　CriteriaRange:=Worksheets("賞味期限切れ").Range("A1:A2"), _
　　CopyToRange:=Worksheets("賞味期限切れ").Range("A6:G6"), _
　　Unique:=False

　.Range("A4:Z1000").AdvancedFilter _
　　Action:=xlFilterCopy, _
　　CriteriaRange:=Worksheets("その他").Range("A1:A2"), _
　　CopyToRange:=Worksheets("その他").Range("A6:G6"), _
　　Unique:=False

　Application.EnableEvents = True
　End With
End Sub

この方向性で，これ以上出来る事はありません。正直あまり使い良くはありません。

方法２：
別のやり方を考えてみると。

＞Ｖ列に数式を入力し、計算によってＶ列の値に変化があった時にマクロを実行する
＞↓入力する数式は以下の数式です。
=IF(DATE(YEAR(A10),MONTH(A10),DAY(A10))<$A$1,"期限切れ","その他")

この式は，A10セル(若しくはA1)にあなたが何かを入力すると，再計算されます。
もしかするとA10にも式が入っているのかも知れませんが，それならそのA10も，さらに上流のどこかのセルに入力されて動き始めます。
つまり「再計算が走る」ためには，通常はその上流に位置する「このセルに入力」する事で計算チェインが動き始める"引き金のセル"があります。

従って，
変更前：
＞"食品一覧"シートのＶ列の値が変わったら

変更後：
→Ｖ列の式が計算に使っている上流側のＡ列のセルに入力をしたら

変更後マクロ：
　Private Sub Worksheet_Change(ByVal Target As Range)
　　With Worksheets("食品一覧")
　　If Intersect(Target, Range("A1:A10000")) Is Nothing Then Exit Sub
　　’以下は現行のまま

のようになります。

お礼

早速のご回答ありがとうございました！

＞もしかするとA10にも式が入っているのかも知れませんが，それならそのA10も，さらに上流のどこかのセルに入力されて動き始めます。

上流のセル(A10)には、=TODAY()が入っています。

説明が難しく、分かりにくいものになってしまうので省略させていただきますが、ご回答頂いた内容を参考に、結局ブックOPEN時に実行するマクロと、手入力した時に実行するマクロの２種類のマクロを使うことにしました。

下手な説明にもかかわらず、ご理解ご回答いただきましてありがとうございました。とても参考になりました！