|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub Aging()
Dim arr1 As Variant, arr2()
Target = "未清账明细"
Sheets(Target).Select
If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilter
End If
n = Sheets(Target).Cells(Rows.Count, 4).End(xlUp).Row
arr1 = Range("D2:D" & n)
ReDim arr2(1 To UBound(arr1), 1 To 1)
i = 1
For Each m In arr1
age = DateDiff("m", m, Range("R1").Value)
Select Case age
Case Is < 3
arr2(i, 1) = "3个月以内"
Case 3 To 6
arr2(i, 1) = "3至6个月"
Case 7 To 12
arr2(i, 1) = "6至12个月"
Case 12 To 24
arr2(i, 1) = "1至2年"
Case Is > 24
arr2(i, 1) = "2年以上"
End Select
i = i + 1
Next m
Columns("q:q").ClearContents
Range("Q1") = "Aging"
Range("Q2").Resize(UBound(arr2), 1) = arr2
Erase arr1, arr2
End Sub |
|