エクセル 数値結果の値によって日付を入れたい

質問

シート2の2列目にOKが入ると、シート１のC列にOKが入り、更新された日がB列に表示されるようにしたいです。
C列に手入力でOKと入力すればB列に日付が表示されるのですが、C列をVLOOKで呼ぶようにしたら表示されなくなってしまいました。
どのように修正していいのか分かりません。
お教えいただければと思います。よろしくお願いいたします。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim TgRng As Range
Set TgRng = Intersect(Range("C1:C2000"), Target)
If Not TgRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In TgRng
If Rng.Value = "OK" Then
Rng.Offset(, -1).Value = Date
End If
Next
Application.EnableEvents = True
End If
Set TgRng = Nothing
End Sub

回答No.7

mt2015 · ベストアンサー

ANo.1です。
手入力するのはシート2のB列なんですね？
Worksheet_Change　は、そのシートに何か入力された場合に発生するイベントです。演算結果が変わっただけでは動きません。シート2に手入力するなら、シート2のWorksheet_Changeにマクロを組む必要が有ります。

で、シート2のWorksheet_Changeにマクロを組む例の回答は既にたくさん出ているので、変化球を。

シートの再計算が走ると、発生するWorksheet_Calculateと言うイベントが走ります。
シート1に以下のマクロを入れてみてください。
これですと、シート2に手入力してシート1で再計算が走ると、シート1のC列の値が「OK」で、B列が空の行を見つけるとB列に日付を入れます。

Private Sub Worksheet_Calculate()
　　Dim Rng As Range
　　For Each Rng In Range("C1:C2000")
　　　　If (Rng.Value = "OK") And (Rng.Offset(, -1).Value = "") Then
　　　　　　Rng.Offset(, -1).Value = Date
　　　　End If
　　Next
End Sub

お礼

できました！！
こんなに簡単にできてしまうなんて・・・。
びっくりです。
ありがとうございました。

回答No.6

watabe007

こんばんは
Sheet2のChangeイベントを使って
Sheet2のB列にOKが書かれたらSheet1のB列に日付
C列に"OK"と書き込まれるようにしました。
以下のコードをSheet2のシートモジュールに貼り付けてください。
Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim Rng As Range, myR As Variant
　　Dim TgRng As Range
　　Set TgRng = Intersect(Range("B2:B2000"), Target)
　　If TgRng Is Nothing Then Exit Sub
　　With Worksheets("Sheet1")
　　　　For Each Rng In TgRng
　　　　　　myR = Application.Match(Cells(Rng.Row, "A"), .Columns(1), 0)
　　　　　　If IsError(myR) Then
　　　　　　ElseIf Rng.Value = "OK" Then
　　　　　　　　.Cells(myR, "B").Value = Date
　　　　　　　　.Cells(myR, "C").Value = "OK"
　　　　　　ElseIf Rng.Value = "NG" Then
　　　　　　　　.Cells(myR, "B").Value = ""
　　　　　　　　.Cells(myR, "C").Value = "NG"
　　　　　　End If
　　　　Next
　　End With
End Sub

お礼

ありがとうございました！！

回答No.5

imogasi

>C列をVLOOKで呼ぶようにしたら表示されなくなってしまいました。
Ｓｈｅeｔ１のセルを参照するＳｈｅｅｔ２の関数を入れていて、Sheet1の参照セルの値が変わり、連れて、Ｓｈｅｅｔ２の関数の値が変わっても、Ｓｈｅｅｔ２のＣｈａｎｇｅイベントに、引っかからないのでは？
過去の数少ない昔のrivate Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Value = "OK" Then
MsgBox "Sheet2のＢ列の値がOKに変わった"
Worksheets("Sheet1").Cells(Target.Row, "B") = Date
End If
End Sub
小生の過去の数少ないイベントのVBAの経験の記憶から言うが、間違っていたらご免。
改善法や批判を待ちます。
例　Ｓｈｅｅｔ１のＣ２はVLOOKUP関数で値を出す。
Ｓｈｅｅｔ２のＢ２に、=Sheet1!C2
Changeイベントの解説は
＞セルが変更された時に発生します。
ですが、手入力をしないとだめなのでは。
そのほかに、
Sub test21()
Worksheets("Sheet2").Cells(3, "B").Value = "OK"
End Sub
の実行でSheet2のB3の値を代入した場合も、Changeイベントは起こった。
参考　手入力でSheet2のB列の値が変わったことをとらえるなら、下記コードで簡単かと。
Sheet2のイベント・プロシを書く画面に
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Value = "OK" Then
MsgBox "Sheet2のＢ列の値がOKに変わった"
Worksheets("Sheet1").Cells(Target.Row, "B") = Date
End If
End Sub

お礼

おっしゃる通りです・・。
シート２の数値が変わっているんですもんね！
お恥ずかしい・・・。
ありがとうございました！！

回答No.4

chayamati

こんばんは
セルC2の式は=VLOOKUP(A2,$E$2:$F$7,2,FALSE)です。
1.第2パラメータは絶対アドレスで定義して、セルC7までコピペ。
　範囲で設定直後でF4キーを押下
2.第3パラメータはFalse
3.VLOOKUPの結果が０の処理を加えて
=if(VLOOKUP(A2,$E$2:$F$7,2,FALSE)=0,"",VLOOKUP(A2,$E$2:$F$7,2,FALSE))

お礼

ありがとうございました。
マクロを修正し、解決いたしました！

回答No.3

HohoPapa

画像をポストし損ねたようなので再度。

ちょっと泥臭い（力技的な）コードですがいかがでしょうか？
なお、Sheet2のシートモジュールに配置することを想定しています。
また、OKと埋まっているセルをOKに置き換えても
日付の書き換えが起きます。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

　Dim RowCounter As Long
　Dim ChgNum As Long

　With ThisWorkbook.Sheets("Sheet1")

　　If Target.Cells.Count <> 1 Then Exit Sub
　　If Target.Column <> 2 Then Exit Sub
　　If Target.Value <> "OK" Then Exit Sub
　　If Me.Cells(Target.Row, 1).Value = "" Then Exit Sub

　　ChgNum = Me.Cells(Target.Row, 1).Value
　　RowCounter = 1
　　Do
　　　If .Cells(RowCounter, 1).Value = "" Then Exit Do
　　　If .Cells(RowCounter, 1).Value = ChgNum Then
　　　　.Cells(RowCounter, 2).Value = Date
　　　End If
　　　RowCounter = RowCounter + 1
　　Loop

　End With

End Sub

お礼

私にとっては、力技でもできることがすごいです・・・。
ありがとうございました！！

回答No.2

HohoPapa

ちょっと泥臭い（力技的な）コードですがいかがでしょうか？
なお、Sheet2のシートモジュールに配置することを想定しています。
また、OKと埋まっているセルをOKに置き換えても
日付の書き換えが起きます。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

　Dim RowCounter As Long
　Dim ChgNum As Long

　With ThisWorkbook.Sheets("Sheet1")

　　If Target.Cells.Count <> 1 Then Exit Sub
　　If Target.Column <> 2 Then Exit Sub
　　If Target.Value <> "OK" Then Exit Sub
　　If Me.Cells(Target.Row, 1).Value = "" Then Exit Sub

　　ChgNum = Me.Cells(Target.Row, 1).Value
　　RowCounter = 1
　　Do
　　　If .Cells(RowCounter, 1).Value = "" Then Exit Do
　　　If .Cells(RowCounter, 1).Value = ChgNum Then
　　　　.Cells(RowCounter, 2).Value = Date
　　　End If
　　　RowCounter = RowCounter + 1
　　Loop

　End With

End Sub

回答No.1

mt2015

そりゃあ、C列に入っているのはセル式のままで変更されていませんから。
手入力で変更するのはシート1のA列で良いのでしょうか？
で、あれば取りあえずこんな感じに変更しては。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim Rng As Range
　　Dim TgRng As Range
　　Set TgRng = Intersect(Range("A1:A2000"), Target)
　　If Not TgRng Is Nothing Then
　　　　Application.EnableEvents = False
　　　　For Each Rng In TgRng
　　　　　　If Rng.Offset(, 2).Value = "OK" Then
　　　　　　　　Rng.Offset(, 1).Value = Date
　　　　　　End If
　　　　Next
　　　　Application.EnableEvents = True
　　End If
　　Set TgRng = Nothing
End Sub

補足

申し訳ありません。
説明がうまくできていなかったようです。
シート１のC列が変更されたらB列に変更日を入力させたいです。
C列はVLOOKの式を使いたいです。
つまりシート２の2列目にOKが入れば自動的にシート１のC列にOKが入って、B列に日付が入るようにしたいのです。
説明が下手で申し訳ありません。