|
Sub 一年以上未使用的材料()
Dim arr
arr = Sheet1.Range("a4:al" & Sheet1.Cells(Rows.Count, 1).End(xlUp).Row)
ReDim brr(1 To UBound(arr), 1 To 2)
For j = 2 To UBound(arr, 2)
If arr(1, j) = "" Then arr(1, j) = arr(1, j - 1)
Next j
For i = 3 To UBound(arr)
k = 0
For j = 2 To UBound(arr, 2)
Do While arr(i, j) = ""
k = k + 1
If k = 1 Then
qr = arr(1, j) & arr(2, j)
qy = Val(arr(2, j)) '起始日期
End If
j = j + 1
If j > UBound(arr, 2) Then Exit Do
Loop
If k >= 12 Then
n = n + 1
j = j - 1
zr = arr(1, j) & arr(2, j) '终止日期
brr(n, 1) = arr(i, 1)
brr(n, 2) = qr & "-" & zr & ":共" & k & "个月"
qr = "": zr = ""
Exit For
Else
qr = "": k = 0
End If
Next j
Next i
Sheet2.Columns("d:e").Clear
Sheet2.[d1].Resize(n, 2) = brr
End Sub
|
|