エクセル マクロ等を利用した各列ごとのセル最終更新日の自動入力について

質問です

エクセルで、各列の個数の増減の最終更新日が自動で入力されるシートを作りたいです。

添付した画像のような構成で、個数のセルにはオートSUMでF以降の個数の増減が各列に入っています。この個数セルの表示が変わった時に最終更新日が更新されるようにするにはどうすればよいのでしょうか？


検索してマクロを用いた最終更新日の入力を探して真似しましたが、参照するのが式の入ってるセルであるせいか、最終更新日が変わらないシートになってしまいます。

式の表記を参照するのが無理なのであれば、F列以降の変更があるごとに最終更新日が反映される構成でも大丈夫です。

もし、詳しい方がおられたらご教授願います。コピペで使えるコード等教えていただけると幸いです。

No.4 回答者： goomania 回答日時： 2021/01/15 16:44

No.2です。

前回のVBAの回答で、解説に合わせてシートモジュールのVBAコードを修正したつもりだったのですが、修正前のコードを投稿してしまい、結果として解説とコードに相違があるものになっていました。
修正後のコードを投稿してお詫びいたします。なお、ブックモジュールに修正はありません。

Dim col() As Long
Dim d() As Variant
Private Sub Worksheet_Activate()
Dim r As Long
Dim i As Long
r = Cells(Rows.Count, 1).End(xlUp).Row
ReDim col(r)
ReDim d(r)
For i = 2 To r
col(i) = Cells(i, Columns.Count).End(xlToLeft).Column
If Cells(i, 3).Value = "" Then
d(i) = ""
Else
d(i) = Cells(i, 3).Value
End If
Next
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Or Target.Count > 1 Then Exit Sub
If Target.Column < 6 Then Exit Sub
Application.EnableEvents = False
If Target.Value <> "" And col(Target.Row) + 1 = Target.Column Then
Cells(Target.Row, 3).Value = Date
Else
If Target.Value = "" And col(Target.Row) + 1 = Target.Column Then
Cells(Target.Row, 3).Value = d(Target.Row)
End If
End If
Application.EnableEvents = True
End Sub

No.3 回答者： goomania 回答日時： 2021/01/12 18:10

No.2です。

前回回答でマクロを使用した例をお示ししましたので、関数を使用した例もお示ししておきます。

添付画像のように、各名称ごとに、数値を入力するセルに対して、1行目に予め連続日付入力しておいて(あるいは変動のあった日付を入力して)から、数値を入力するルールとします。
従って名称によっては変動の無い日があるため空欄が発生します。

こうすると、各名称の数値が入力されている最右列の日付が最終更新日付になります。
それを関数で表示させるようにします。例えばF列からZ列までが入力可能だとすると、C2に記述する数式は

=IFERROR(INDIRECT("R1C"&MAX(INDEX(($F2:$Z2<>"")*(COLUMN($F2:$Z2)),0)),0),"")

となり、これを下方向へオートフィル等でコピーします。
添付画像を参照してください。

No.2 回答者： goomania 回答日時： 2021/01/12 16:03

ご質問者が最終更新日を自動表示しようとしているシートが、仮にsheet1だとすると、sheet1のシートモジュールに以下のvbaを記述します。

Dim col() As Long
Dim d() As Variant
Private Sub Worksheet_Activate()
Dim r As Long
Dim i As Long
r = Cells(Rows.Count, 1).End(xlUp).Row
ReDim col(r)
ReDim d(r)
For i = 2 To r
col(i) = Cells(i, Columns.Count).End(xlToLeft).Column
If Cells(i, 3).Value = "" Then
d(i) = ""
Else
d(i) = Cells(i, 3).Value
End If
Next
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Or Target.Count > 1 Then Exit Sub
If Target.Column < 6 Then Exit Sub
Application.EnableEvents = False
If Target.Value <> "" And col(Target.Row) < Target.Column Then
Cells(Target.Row, 3).Value = Date
Else
If Target.Value = "" And Target.Column > col(Target.Row) Then
Cells(Target.Row, 3).Value = d(Target.Row)
End If
End If
Application.EnableEvents = True
End Sub

残念ながら、新しいウィンドウが開くときはWorksheet_Activateイベントが発生しないので、最終更新日を自動表示しようとしているシートの他に、空のシートでよいのでダミーのsheet2を用意します。そのうえで、sheet1のWorksheet_Activateイベントを発生させるため、ブックモジュールに以下のvbaを記述します。

Private Sub Workbook_open()
ThisWorkbook.Worksheets(2).Activate
ThisWorkbook.Worksheets(1).Activate
End Sub

このマクロでは、ブックを開いたときに保存されていた状態の最終更新日と入力各行の最右列の列数を取得し、Worksheet_Changeイベントで、最右列の一つ右側のセルが更新されたら、最終更新日を更新します。ただし、行を間違って入力してしまった場合を考え、保存時の最右列より一つ右のセルをクリアすると最終更新日は保存時のものに戻ります。

つまり、このマクロは、りんご、ぶどうなどの一つの名称が一日に使うセルは一つだけという前提です。
一つの名称が一日にセルを複数使用してよいという前提だとすると、このマクロでは一旦ブックを保存してから、再度ブックを開いて作業することになります。

No.1 回答者： Qchan1962 回答日時： 2021/01/06 00:30

こんばんは、

＞コピペで使えるコード等教えていただけると幸いです。
ここは、依頼をする場ではありませんが、、自己学習でサンプルを

ご質問の内容がイマイチわかりませんが、
仕様として、Excelを開いた時と作業をされて閉じる時のE列の数値の違いで
閉じる時の日付をC列の対象行に書き込みを行えれば良いのでしょうか？

解釈が違うかも知れませんが、参考として

ThisWorkbookモジュールに

Option Explicit
Dim MyAry As Variant
Const SH_NAME As String = "Sheet1"
Private Sub Workbook_Open()
Dim sh As Worksheet
Set sh = Sheets(SH_NAME)
MyAry = sh.Range("E2:" & "E" & sh.Cells(Rows.Count, "E").End(xlUp).Row)
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Long
Dim ChAry As Variant
Dim sh As Worksheet
Set sh = Sheets(SH_NAME)
ChAry = sh.Range("E2:" & "E" & sh.Cells(Rows.Count, "E").End(xlUp).Row)
For i = 1 To UBound(MyAry)
If MyAry(i, 1) <> ChAry(i, 1) Then
sh.Cells(i + 1, "C").Value = Date
End If
Next i
End Sub

"Sheet1"は、対象のシート名に変更してください。
コピペしたら　保存して　Private Sub Workbook_Open()をF5などで実行してください。

E列の値が変わる作業を行い、閉じてください。（保存する）
書き込みは閉じるときに実行されます。

閉じたブックを再度　開いてみてください。

検証はコピーファイルなどで行い自己責任でお願いします。

この回答へのお礼

Qchan1962さん、回答ありがとうございます。

解釈していただいたその通りです、思っていた機能を持ったシートが出来ました。ありがとうございます。

また近々シートを作らなければいけないので、教えていただいた内容を参考にして自分で作ってみたいと思います。

お礼日時：2021/01/10 21:59