当用户在相邻单元格中输入信息时，自动填充单元格中的日期 [英] Auto-fill the date in a cell, when the user enters information in an adjacent cell

问题描述

我有一个电子表格，人们可以在那里输入项目更新，然后更新日期。发生了什么事情是人们忘记了他们的笔记。有没有办法让日期单元格自动输入当前/输入日期？

我假设一个if函数会这样做？

解决方案

这个事件宏将日期列在 B 列中在 A列中输入一个值。宏应安装在工作表代码区域中：

  Private Sub Worksheet_Change（ByVal Target As Range）
 Dim A As Range，B As Range，Inte As Range，r As Range 
 Set A = Range（A：A）
 Set Inte =相交（A，目标）
如果Inte没有，然后退出Sub 
 Application.EnableEvents = False 
对于每个r In In 
 r.Offset（0，1）.Value =日期
下一步r 
 Application.EnableEvents = True 
 End Sub 
  

因为它是工作表代码，它很容易安装和自动使用：

1. 右键单击底部附近的选项卡名称的Excel窗口


2. 选择查看代码 - 这会打开一个VBE窗口


3. 将东西粘贴并关闭VB E窗口

如果您有任何疑虑，请先试用试用版。

如果保存工作簿，宏将被保存。
如果您在2003年以后使用Excel版本，则必须将
作为.xlsm而不是.xlsx

将文件另存为宏：

1. 打开上面的VBE窗口


2. 清除代码


3. 关闭VBE窗口

要了解有关宏的更多信息，请参阅：

http://www.mvps.org/dmcritchie/ excel / getstarted.htm

和

http://msdn.microsoft.com/en-us/library/ee814735（v = office。 14）.aspx

要了解有关事件宏（工作表代码）的更多信息，请参阅：

http://www.mvps.org/dmcritchie/excel/event.htm

必须启用此宏才能启用宏！

更改代码以使用不同的列。

编辑＃1：

此版本不会覆盖列 B 中已经存在的素材：

  Private Sub Worksheet_Change（ByVal Target As Range）
 Dim A As Range，B As Range，Inte As Range，r As Range 
 Set A = Range（A：A ）
 Set Inte = Intersect（A，Target）
如果Inte没有，则退出Sub 
 Application.EnableEvents = False 
对于每个r In In 
如果r .Offset（0，1）.Value =Then 
 r.Offset（0，1）.Value = Date 
 End If 
 Next r 
 Application.EnableEvents = True 
 End Sub 
  

I have a spread sheet, where people can enter project updates and then the date of the update. What is happening is that people are forgetting to date their notes. Is there a way to have the date cell autopoplute the current/date of entry?

I am assuming an if function would do it?

解决方案

This event macro will place the date in column B if a value is entered in column A. The macro should be installed in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, B As Range, Inte As Range, r As Range
    Set A = Range("A:A")
    Set Inte = Intersect(A, Target)
    If Inte Is Nothing Then Exit Sub
    Application.EnableEvents = False
        For Each r In Inte
            r.Offset(0, 1).Value = Date
        Next r
    Application.EnableEvents = True
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Change the code to use different columns.

EDIT#1:

This version will not overwrite material already present in column B:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, B As Range, Inte As Range, r As Range
    Set A = Range("A:A")
    Set Inte = Intersect(A, Target)
    If Inte Is Nothing Then Exit Sub
    Application.EnableEvents = False
        For Each r In Inte
            If r.Offset(0, 1).Value = "" Then
               r.Offset(0, 1).Value = Date
            End If
        Next r
    Application.EnableEvents = True
End Sub

这篇关于当用户在相邻单元格中输入信息时，自动填充单元格中的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！