|
本帖最后由 brothersonline 于 2013-11-26 13:23 编辑
请教各位:如何利用函数或宏,根据前两列内容自动生成第三列内容?
1、若:B列显示是星期一,则:E列为:计划、巡诊;
2、若:B列显示 是星期五,则:E列为:小结;
3、若:C列为"张 健",则:E列为:值班;
4、若:B列显示 是星期一、同时C列为"张 健",则:E列为:计划、巡诊、值班;
5、若:B列显示 是星期五、同时C列为"张 健",则:E列为:小结、值班;
6、若不满足上述条件,E列值不发生变化。
请教:如何以函数或宏在E列自动生成上述内容?(详见附件)
本帖最后由 fjmxwrs 于 2013-11-26 14:03 编辑
brothersonline 发表于 2013-11-26 13:24
呵呵,是我表达不清楚,那就不要放假的条件了。
我这里给你写了元旦三天假,国庆7天假的,你看下是不是这个意思,其他假期,你自己加进去就行了 - Sub test()
- Dim arr, brr(), iRow%, x%, y%, i%
- With Sheets("记事本")
- iRow = .Cells(Rows.Count, 1).End(xlUp).Row
- arr = .Range("B4:C" & iRow)
- ReDim brr(1 To UBound(arr))
- For x = 1 To UBound(arr)
- If arr(x, 2) = "张 健" Then
- If arr(x, 1) >= DateSerial(Year(Date), 1, 1) And arr(x, 1) < DateSerial(Year(Date), 1, 4) Or arr(x, 1) >= DateSerial(Year(Date), 10, 1) And arr(x, 1) < DateSerial(Year(Date), 10, 8) Then
- brr(x) = "放假、值班"
- ElseIf Weekday(arr(x, 1), vbMonday) = 1 Then
- brr(x) = "计划、巡诊、值班"
- ElseIf Weekday(arr(x, 1), vbMonday) = 5 Then
- brr(x) = "小结、值班"
- Else
- brr(x) = "值班"
- End If
- Else
- If arr(x, 1) >= DateSerial(Year(Date), 1, 1) And arr(x, 1) < DateSerial(Year(Date), 1, 4) Or arr(x, 1) >= DateSerial(Year(Date), 10, 1) And arr(x, 1) < DateSerial(Year(Date), 10, 8) Then
- brr(x) = "放假"
- ElseIf Weekday(arr(x, 1), vbMonday) = 1 Then
- brr(x) = "计划、巡诊"
- ElseIf Weekday(arr(x, 1), vbMonday) = 5 Then
- brr(x) = "小结"
- Else
- brr(x) = ""
- End If
- End If
- Next x
- .Range("E4").Resize(UBound(brr), 1) = Application.Transpose(brr)
- End With
- End Sub
复制代码
|
|