时间戳

首页 > 解决方案 > Using the Year in one cell to determine the contents for another cell

excel - Using the Year in one cell to determine the contents for another cell

问题描述

What I want to do is have a value put into a column based on the year in the other column. The year column is in MM/DD/YYYY format. I am not a very experienced VBA programmer, but I am trying to learn it as fast as I can.

I've already tried using the Year() function in an IF statement to see if it will show the text I want it to, but it shows nothing in the cell. 

Dim SrchRng As Range, cel As Range

Set SrchRng = Range("D1:D9")

For Each cel In SrchRng
    If IsEmpty(cel) And Year(cel.Offset(0, -1)) = Year(2020) Then
        cel.Offset(0, -2).Value = "Test"
    End If
Next cel

Nothing shows up in the cel -2 cell and I am unsure what to try next. Any help would be greatly appreciated.

标签： excelvba

解决方案

Year(2020)如前所述，返回 1905 这是因为它适用于未以任何方式格式化的原始编号/序列号。作为日期写入单元格中的日期将被识别为日期，并且值会更改，但通过格式化看起来会相同。例如今天的日期是 43650 没有格式化并Year(43650)返回 2019

2020如果您想将每个Year(date)与该数字进行比较，只需如下所示

Sub yearTest()
    Dim SrchRng As Range, cel As Range
    Set SrchRng = Range("D1:D9")

    For Each cel In SrchRng
        If IsEmpty(cel) And Year(cel.Offset(0, -1)) = 2020 Then
            cel.Offset(0, -2).Value = "Test"
        End If
    Next cel
End Sub

您也可以在工作表中尝试公式，例如单元格 D1 中的 =Year(C1)，然后更改 C1 中日期的格式以查看不存在的效果。

推荐阅读