我正在跟蹤一個大型 Excel 檔案中的付款。在 C 欄中，我有我們收到付款的日期，在 I 欄中，我有付款日期。我想計算 C 列（當我們 rcvd invc 時）和 I 列（支付 invc 時）之間的月數。
我還希望將結果（月數）添加到我的資料右側的列中，而不僅僅是在輸出訊息框中。我可以在 VBA EXCEL 中執行此操作嗎？
我使用了下面的代碼，但我不知道如何更改 msgbox 結果以顯示在新列上......
Sub DateDiff_Example1()
Dim Date1 As Date
Dim Date2 As Date
Dim Result As Long
Date1 = Range("C2")
Date2 = Range("I2")
Result = DateDiff("M", Date1, Date2)
MsgBox Result
End Sub
uj5u.com熱心網友回復：
一種方法是使用如下回圈：
Sub DateDiff_Example2()
Dim Date1 As Date
Dim Date2 As Date
Dim Result As Long
Dim row As Long
Dim last_row As Long
Dim s As Worksheet
Set s = ActiveSheet ' operate on the active sheet
'set s= thisworkbook.Worksheets("Sheet1")' operate on a specific sheet
last_row = s.Cells(s.Rows.Count, "C").End(xlUp).row
For row = 2 To last_row
Date1 = s.Cells(row, "c").Value
Date2 = s.Cells(row, "I").Value
s.Cells(row, "J").Value = DateDiff("M", Date1, Date2)
Next
End Sub
當心。這將替換“J”列中的所有資料。如果“J”列不正確，請務必將以下代碼中的“J”更改為輸出列字母：
s.Cells(row, "J").Value
uj5u.com熱心網友回復：
另一種方法是使用同名的 Excel 函式。只需將此公式放在您想要日期的列的第 2 行中：
=DATEDIF(C2,I2,"M")
uj5u.com熱心網友回復：
使用計算月差DateDiff
Option Explicit
Sub UpdateMonthDifferences()
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
Dim rrg As Range
Dim rCount As Long
With ws.Range("C2")
Dim lCell As Range: Set lCell = .Resize(ws.Rows.Count _
- .Row 1).Find("*", , xlFormulas, , , xlPrevious)
If lCell Is Nothing Then Exit Sub ' no data in column range
rCount = lCell.Row - .Row 1
Set rrg = .Resize(rCount)
End With
Dim prg As Range: Set prg = rrg.EntireRow.Columns("I")
Dim rData As Variant, pData As Variant
If rCount = 1 Then
ReDim rData(1 To 1, 1 To 1): rData(1, 1) = rrg.Value
ReDim pData(1 To 1, 1 To 1): pData(1, 1) = prg.Value
Else
rData = rrg.Value: pData = prg.Value
End If
Dim r As Long
For r = 1 To rCount
If IsDate(rData(r, 1)) And IsDate(pData(r, 1)) Then
rData(r, 1) = DateDiff("m", rData(r, 1), pData(r, 1))
Else
rData(r, 1) = Empty
End If
Next r
Dim drg As Range: Set drg = rrg.EntireRow.Columns("J")
drg.Value = rData
MsgBox "Month differences updated.", vbInformation
End Sub
Office 365：使用 DATEDIF 進行評估
- Office 365 用戶應該能夠擺脫以下簡化。
- 我沒有 365，因此非常感謝您的反饋。
Excel DATEDIF function
Sub UpdateMonthDifferencesEval()
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
Dim rrg As Range
With ws.Range("C2")
Dim lCell As Range: Set lCell = .Resize(ws.Rows.Count _
- .Row 1).Find("*", , xlFormulas, , , xlPrevious)
If lCell Is Nothing Then Exit Sub ' no data in column range
Set rrg = .Resize(lCell.Row - .Row 1)
End With
Dim rAddress As String: rAddress = rrg.Address
Dim pAddress As String: pAddress = rrg.EntireRow.Columns("I").Address
Dim drg As Range: Set drg = rrg.EntireRow.Columns("J")
drg.Value = ws.Evaluate("DATEDIF(" & rAddress & "," & pAddress & ",""M"")")
MsgBox "Month differences updated.", vbInformation
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/447202.html