100¦~­È¤éªí ¤w¸Ñ¨M, ¦p¦ó, ­È¤éªí

h60327

123.rar (16.06 KB)

¤U¸ü¦¸¼Æ: 22

2011-10-28 10:12

³o¬O100¦~ªº­È¤éªí¡A¤§«e¤w·Ð½ÐªO¤j­×¥¿¡A¦ý²{¦bµo²{¤@­Ó°ÝÃD¡A¥»¨Ó­È¤éªí¤¤¥­±`¤é»Ý¦©°£¶g¥ð¤Î¨Ò°²¤é(MÄæ)¡A¦ý¦]¦³¸É¤W¯Z°ÝÃD(OÄæ)¡A[¨Ò¦p:1/27©ñ°²¦Ó©ó2/4¸É¤W¯Z]¡A½Ð°Ý¦U¦ìªO¤j³o­n³o­n¦p¦ó¸Ñ¨M©O¡HÁÂÁ¤j®a¤F

man65boy

¦^´_ 5# Hsieh

¤Ó±j¤F¡A·PÁ¶W¯Åª©¤j¡Aµ¹©ó¦¹ÃD¤£¤@¼Ëªº±ø¥ó§ó§ï°Ñ¦Ò¡AÁÂÁÂ~~

Hsieh

1. Private Sub CommandButton1_Click()
   
2. [A2:H65536].ClearContents
   
3. Dim ds As Object, ds1 As Object, d As Object, Arr(1 To 65536, 0 To 4), Men, i#, test$, k%, temp$, s&
   
4. Men = Range([J2], [J65536].End(xlUp)).Value
   
5. Set ds = CreateObject("Scripting.Dictionary") '°ê©w°²¤é
   
6. Set ds1 = CreateObject("Scripting.Dictionary") '¸É¤W¯Z
   
7. Set d = CreateObject("Scripting.Dictionary") '¸É¤W¯Z
   
8. r = [M65535].End(xlUp).Row
   
9. For i = 2 To r '°ê©w°²¤é
   
10. temp = Month(Cells(i, 13)) & "," & Day(Cells(i, 13))
    
11. ds.Add temp, i
    
12. Next
    
13. r = [O65536].End(xlUp).Row
    
14. For i = 2 To r '¸É¤W¯Z
    
15. temp = Month(Cells(i, 15)) & "," & Day(Cells(i, 15))
    
16. ds1.Add temp, i
    
17. Next
    
18. r = DateAdd("yyyy", 1, Range("K1")) - 1
    
19. For i = Range("K1") To r
    
20. test = Month(i) & "," & Day(i)
    
21. If Weekday(i, vbMonday) = 6 Then d(Month(i)) = d(Month(i)) + 1
    
22.    If (Weekday(i, vbMonday) <= 6 And ds.Exists(test) = False) Or ds1.Exists(test) = True Then  '¶g¤@¦Ü¶g¤­¨Ã¦©°£MÄæ°ê©w°²¤é¥[¤J¸É¤W¯Z¤é
    
23.      If d(Month(i)) Mod 2 = 0 And Weekday(i, vbMonday) = 6 Then GoTo 10 '°¸¼Æ¶g¤»¸õ¹L
    
24.      s = s + 1
    
25.      Arr(s, 1) = i
    
26.      Arr(s, 2) = Month(i)
    
27.      Arr(s, 3) = Day(i)
    
28.      Arr(s, 4) = Weekday(i, 2)
    
29.      k = IIf(s Mod UBound(Men) = 0, UBound(Men), s Mod UBound(Men))
    
30.      Arr(s, 0) = Men(k, 1)
    
31.     End If
    
32. 10
    
33. Next
    
34. [A2].Resize(s, 5) = Arr
    
35. 
    
36. Range([E2], [E65536].End(xlUp)).FormulaR1C1 = _
    
37. "=IF(WEEKDAY(RC[-3])=2,""¤@"",(IF(WEEKDAY(RC[-3])=3,""¤G"",(IF(WEEKDAY(RC[-3])=4,""¤T"",(IF(WEEKDAY(RC[-3])=5,""¥|"",(IF(WEEKDAY(RC[-3])=6,""¤­"",(IF(WEEKDAY(RC[-3])=7,""¤»"",(IF(WEEKDAY(RC[-3])=1,""¤é"","""")))))))))))))"
    
38. Range([E2], [E65536].End(xlUp)).Formula = Range([E2], [E65536].End(xlUp)).Value
    
39. 
    
40. End Sub

½Æ»s¥N½X

¦^´_ 4# man65boy

man65boy

¦^´_ 2# Hsieh


«Ü¤£¿ùªº­È¤éªí¡A½Ð°Ý¶W¯ÅªO¥D¡B¦pªG¥H¬Û¦P®æ¦¡¡A¥Î¹j¶g¥ð±Æ¯Z¡A¸Ó¦p¦ó§ó§ï!
ÁÂÁÂ!!!

h60327

­ì¨Ó¦Û¤vªº«ä¸ô¨Ã¨S¦³¿ù
¦ý¦]¥¼¥[¤W³o¤@¬q Set ds1 = CreateObject("Scripting.Dictionary")
³y¦¨µLªk°õ¦æ
¸Ó¾Ç²ßªºÁÙ¬O«Ü¦h¡A¯u¬O·PÁª©¥D¤£§[«ü¾É

Hsieh

¦^´_ 1# h60327

1. Private Sub CommandButton1_Click()
   
2. [A2:H65536].ClearContents
   
3. Dim ds As Object, ds1 As Object, Arr(1 To 65536, 0 To 4), Men, i#, test$, k%, temp$, s&
   
4. Men = Range([J2], [J65536].End(xlUp)).Value
   
5. Set ds = CreateObject("Scripting.Dictionary") '°ê©w°²¤é
   
6. Set ds1 = CreateObject("Scripting.Dictionary") '¸É¤W¯Z
   
7. 
   
8. For i = 2 To [M65536].End(xlUp).Row '°ê©w°²¤é
   
9. temp = Month(Cells(i, 13)) & "," & Day(Cells(i, 13))
   
10. ds.Add temp, i
    
11. Next
    
12. For i = 2 To [O65536].End(xlUp).Row '¸É¤W¯Z
    
13. temp = Month(Cells(i, 15)) & "," & Day(Cells(i, 15))
    
14. ds1.Add temp, i
    
15. Next
    
16. 
    
17. For i = [k1] To DateAdd("yyyy", 1, [k1]) - 1
    
18. test = Month(i) & "," & Day(i)
    
19.    If (Weekday(i, vbMonday) < 6 And ds.Exists(test) = False) Or ds1.Exists(test) = True Then  '¶g¤@¦Ü¶g¤­¨Ã¦©°£MÄæ°ê©w°²¤é¥[¤J¸É¤W¯Z¤é
    
20.      s = s + 1
    
21.      Arr(s, 1) = i
    
22.      Arr(s, 2) = Month(i)
    
23.      Arr(s, 3) = Day(i)
    
24.      Arr(s, 4) = Weekday(i, 2)
    
25.      k = IIf(s Mod UBound(Men) = 0, UBound(Men), s Mod UBound(Men))
    
26.      Arr(s, 0) = Men(k, 1)
    
27.     End If
    
28. Next
    
29. [A2].Resize(s, 5) = Arr
    
30. 
    
31. Range([E2], [E65536].End(xlUp)).FormulaR1C1 = _
    
32. "=IF(WEEKDAY(RC[-3])=2,""¤@"",(IF(WEEKDAY(RC[-3])=3,""¤G"",(IF(WEEKDAY(RC[-3])=4,""¤T"",(IF(WEEKDAY(RC[-3])=5,""¥|"",(IF(WEEKDAY(RC[-3])=6,""¤­"",(IF(WEEKDAY(RC[-3])=7,""¤»"",(IF(WEEKDAY(RC[-3])=1,""¤é"","""")))))))))))))"
    
33. Range([E2], [E65536].End(xlUp)).Formula = Range([E2], [E65536].End(xlUp)).Value
    
34. 
    
35. End Sub

½Æ»s¥N½X