|
发表于 2012-10-5 15:16
|
显示全部楼层
本楼为最佳答案
- Private Sub Workbook_Open()
- Dim arrName, arrDate
- Dim iRow As Long, i As Long
- Dim sName As String
- Dim dDate As Date
-
- iRow = [h2].End(xlDown).Row
- If Cells(iRow, "h") = "" Then Exit Sub
- arrName = Range("h2:h" & iRow).Value
- arrDate = Range("w2:w" & iRow).Value
- For i = 1 To UBound(arrName)
- dDate = arrDate(i, 1)
- If Date < dDate And dDate - Date <= 15 Then
- sName = IIf(sName = "", arrName(i, 1) & " " & arrDate(i, 1), sName & vbCr & arrName(i, 1) & " " & arrDate(i, 1))
- Else
- If Date > dDate Then sName = IIf(sName = "", arrName(i, 1) & " " & arrDate(i, 1), sName & vbCr & arrName(i, 1) & " " & arrDate(i, 1))
- End If
- Next
- If Len(sName) > 0 Then
- MsgBox "合同马上到期人员名单:" & vbCr & sName
- End If
-
- End Sub
复制代码 你踩中我的地雷了,我是会判断A列最后一行数据是否为空,如果为空,就自动结束了。 |
|