|
我也是在学习中,以下供参考
Private Sub Workbook_Open()
str1 = "近期合同已到期人员名单:"
str2 = "今天合同到期人员名单:"
str3 = "近期合同即将到期人员名单:"
For Each Sh In Worksheets
With Sh
r = Sh.Cells(Rows.Count, "G").End(3).Row
If r > 1 Then
For j = 3 To r
If Year(Sh.Cells(j, "G").Value) = Year(Now) Then
If Date - CDate(Sh.Cells(j, "G").Value) > 0 And Date - CDate(Sh.Cells(j, "G").Value) < 10 Then
str1 = str1 & Chr(10) & Sh.Name & Sh.Cells(j, 2) & "-" & Sh.Cells(j, "G")
End If
If Date - CDate(Sh.Cells(j, "G").Value) = 0 Then
str2 = str2 & Chr(10) & Sh.Name & Sh.Cells(j, 2) & "-" & Sh.Cells(j, "G")
End If
If CDate(Sh.Cells(j, "G").Value) - Date > 0 And CDate(Sh.Cells(j, "G").Value) - Date < 10 Then
str3 = str3 & Chr(10) & Sh.Name & Sh.Cells(j, 2) & "-" & Sh.Cells(j, "G")
End If
End If
Next j
End If
End With
Next Sh
If InStr(str1, Chr(10)) > 0 Then MsgBox str1
If InStr(str2, Chr(10)) > 0 Then MsgBox str2
If InStr(str3, Chr(10)) > 0 Then MsgBox str3
End Sub |
评分
-
1
查看全部评分
-
|