|
- Sub 生成短信()
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets("通讯录").Range("a3:c" & Sheets("通讯录").[a65536].End(3).Row) '通讯录
- For i = 1 To UBound(arr)
- d(arr(i, 1) & arr(i, 2)) = arr(i, 3)
- Next
- With Sheets("排班表")
- arr = .Range("a3:k" & .[c65536].End(3).Row)
- For i = 1 To UBound(arr)
- arr(i, 5) = d(arr(i, 3) & arr(i, 4))
- arr(i, 8) = d(arr(i, 6) & arr(i, 7))
- tel1 = arr(i, 8)
- If i < UBound(arr) Then
- x2 = arr(i + 1, 3) & arr(i + 1, 4)
- tel2 = d(x2)
- arr(i, 10) = "'" & tel1 & "," & tel2
- arr(i, 11) = "今天(" & Month(arr(i, 2)) & "月" & Day(arr(i, 2)) & "日)晚班值班人员是" & arr(i, 3) & _
- ",明天(" & Month(arr(i, 2) + 1) & "月" & Day(arr(i, 2) + 1) & "日)白班值班人员是" & arr(i, 6) & ",请按要求值班"
- Else
- arr(i, 10) = tel1
- arr(i, 11) = "今天(" & Month(arr(i, 2)) & "月" & Day(arr(i, 2)) & "日)晚班值班人员是" & arr(i, 3) & ",请按要求值班"
- End If
- Next
- .Range("a3:k" & .[c65536].End(3).Row) = arr
- End With
- End Sub
复制代码 |
|