エクセルのVBA。時間を自動で表示させたい

質問

こんにちは。
よろしくお願い致します。
在庫数が０になった時間をK列に表示したいのですが、うまく出来ません。
H列、I列、J列、K列を使います。
H列は自分で数字を入力しています。
I列はCOUNTIFで他のシートから数字を持ってきています。
J列には「H－I」の数式が入っています。
そしてK列にJ列の数字が０になった時の時間を表示したいです。

どうぞよろしくお願い致します。

回答No.4

tom04 · ベストアンサー

No.1・3です。

＞シート３のH列が朝在庫数で、
＞I列が販売数、ここにcountifが入っていて、シート１のAE列の名称を数えています。
＞J列が現在残っている在庫です。
＞K列に販売終了時間を入れたいです

というコトは前回のコードはSheet3のシートモジュールにしなければなりませんが、
そのようになっているのでしょうか？
その上でそのような状態になるのであれば、別の方法を考えなければなりませんね。

前回のコードは再計算されるたびにマクロが走ってしまいます。
それが原因かもしれません。

別マクロで前回のコードを記載してみてはどうでしょう？

もちろん「再計算」されるたびにマクロが実行されるのではなく、コマンドボタン等でご自身でマクロを実行させます。

もう一つ気になるのがCOUNTIF関数の「検索条件」がコマンドボタンをクリックするたびに変わるのでしょうか？
もしそうであれば、一旦K列データを消去する必要があるように思うのですが・・・

お手元のSheetの動きがこちらでは判断できませんので、
とりあえずはこの程度で・・・m(_ _)m

お礼

こんばんは。
なんとか出来ました！！（＾＾）
何度も質問してしまい、申し訳ございませんでした。
この度は大変助かりました！
ありがとうございました。

回答No.5

山田 太郎（@f_a_007）

【回答No.1でほぼ解決】

横から失礼！介入した理由は、それはtom04氏の最初の回答がスーパー正解だと思うからです。

＜私のテストでは問題なし！＞

tom04氏の最初の回答の考え方とコードとをそのまんまコピペしてテスト。すると、全く問題なく動きましたよ。

Private Sub Worksheet_Change(ByVal Target As Range)
　　With Cells(Target.Row, "K")
　　　　If (Cells(Target.Row, "J") = 0) Then
　　　　　　If .Font.Color = RGB(0, 0, 0) Then
　　　　　　　　.Value = Now()
　　　　　　　　.NumberFormatLocal = "hh:mm"
　　　　　　　　.Font.Color = RGB(1, 1, 1)
　　　　　　End If
　　　　Else
　　　　　　If Len(.Value & "") > 0 Then
　　　　　　　　.Value = ""
　　　　　　　　.Font.Color = RGB(0, 0, 0)
　　　　　　End If
　　　　End If
　　End With
End Sub

＜J列が新たに’0’になったのを知る手がかり＝文字色＞

　これには、時刻の表示色を利用します。

J=0 AND 文字色=RGB(1, 1, 1)・・・表示更新不要
J=0 AND 文字色=RGB(0, 0, 0)・・・Now()を表示

J<>0の時は、時刻が表示されている場合に限って表示を消して文字色を初期化します。

これで、必要最低限のExcelとのアクセスで目的を達成できると思いますよ。

お礼

こんばんは。
物分りが悪くて申し訳ございません。
先ほど、なんとか出来ました！
どうもありがとうございました！

また何かございましたら、よろしくお願い致します。

回答No.3

tom04

No.1です。

＞列の残りが０になったときに判断できませんか？
おそらくそんな感じがしていたのですが・・・

Changeイベントでは数式によって表示されているセルの変化には対応できませんので
別方法です。

今回もシートモジュールにしてください。
K列のセルの表示形式は「時刻」にしておいてください。
尚、データは2行目以降にあるとします。

Private Sub Worksheet_Calculate()
Dim i As Long
Application.ScreenUpdating = False
For i = 2 To Cells(Rows.Count, "H").End(xlUp).Row
If Cells(i, "J") = 0 Then
If Cells(i, "K") = "" Then
Cells(i, "K") = Now()
End If
Else
Cells(i, "K").ClearContents
End If
Next i
Application.ScreenUpdating = True
End Sub

※　すでに時刻が入っているセルには手を付けず、J列が「0」でK列が空白のセルのみに「時刻」を表示するようにしています。
（反対にK列に時刻が入っていてもJ列が「0」でない場合はK列データを消去しています）

※　I列の「COUNTIF関数」がどこのセルを対照としているのか判らないので
H列の2行目～最終行までループさせています。
データ量が多い場合は若干の時間を要してしまいます。

本来であればCOUNTIF関数の対照セルのChangeイベントにして
その行だけの操作にした方が良いのですが、
とりあえずはこの程度で・・・m(_ _)m

補足

早速ありがとうございます！
エクセルに入れてみたんですが、処理でフリーズしてしまいました。
何か別の方法はございませんでしょうか？
何度も質問してしまい大変申し訳ございません。

シート１にコマンドボタンが多数あり、これを押すとシート１のAE列にその押したコマンドボタンの名称が入るようになってます。
シート３のH列が朝在庫数で、
　　　　　I列が販売数、ここにcountifが入っていて、シート１のAE列の名称を数えています。
　　　　　J列が現在残っている在庫です。
　　　　　K列に販売終了時間を入れたいです。

なにか良い方法をご教授願います。
よろしくお願い致します。

回答No.2

山田 太郎（@f_a_007）

色んなやり方があるでしょう。
例えば、[J列監視プロシージャ]に任せるとか・・・。
一定の間隔で対象のJ列を定期的に見回りして＜0ならばK列に時間を書きこむ＞。
「一定の間隔=30秒」でも別に運用上は問題ないと思います。
もちろん、[J列監視プロシージャ]は常駐ですから、その分、重たくなります。
しかし、大したことはないです。
「一定の間隔=0.01秒」で、入力に応じて図柄を表示。
なんて[常駐プロシージャ]を作りましたが、問題はなかったです。
この利点は、一つ作れば、まあ、色んなケースで応用できること。
それと、面白いというのが一番ですね。

回答No.1

tom04

こんにちは！

色々やり方はあると思いますが、

＞H列は自分で数字を入力しています。
とありますので、H列にデータ入力した時点で判断しています。

シートモジュールです。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Column = 8 And Target.Count = 1 Then
If Cells(Target.Row, "J") = 0 Then
With Cells(Target.Row, "K")
.Value = Now()
.NumberFormatLocal = "h:mm"
End With
End If
End If
End Sub

こんなんではどうでしょうか？m(_ _)m

補足

どうもありがとうございます！
H列は販売前に打ってからいじることがないので、J列の残りが０になったときに判断できませんか？