VBA - 计算列中两个日期值之间的天数差异

Question

我想创建一个脚本，我可以在其中计算两个日期值之间的天数差异 Column A和 Column B如果 Day Value大于 15 标记为 "15 days!" .如果少于没有采取任何行动..

例子:

Column A0 | Column B   | Column C |
01/2/2016 | 01/17/2016 |          |
01/3/2016 | 01/05/2016 |          |
01/4/2016 | 01/20/2016 |          |

输出:

 Column A0 | Column B   | Column C |
 01/2/2016 | 01/17/2016 | 15 days! |
 01/3/2016 | 01/05/2016 |          |
 01/4/2016 | 01/20/2016 | 15 days! |

我的代码:

Dim firstDate As Date, secondDate As Date
 Dim result As Integer

    Set Sheet2 = Workbooks.Open(TextBox2.Text).Sheets(1)

        firstDate = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
        secondDate = Sheet2.Range("B" & Rows.Count).End(xlUp).Row

    If result = DateDiff("d", firstDate, secondDate) > 15 Then
    Sheet2.Cells(result, 3) = 15 days!

  End If

我知道我需要使用 DateDiff 函数来计算天数，但我仍然对如何从 column A 获取值感到困惑至 column B使用和使用它作为 firstdate , second date值(value)观。有人可以帮我解决这个问题吗？!

Answer 1

尝试这个。

Sub CalculateDate()
Dim Result, RowNo As Long
Dim FirstDate, SecondDate As Date
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets(1)

RowNo = 1

    Do Until ws.Cells(RowNo, 1) = ""

    FirstDate = ws.Cells(RowNo, 1)
    SecondDate = ws.Cells(RowNo, 2)

        If DateDiff("d", FirstDate, SecondDate) >= 15 Then
        ws.Cells(RowNo, 3) = "15 DAYS!"
        End If

    RowNo = RowNo + 1

    Loop

End Sub