ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

100¦~­È¤éªí

100¦~­È¤éªí

¥»©«³Ì«á¥Ñ h60327 ©ó 2011-10-28 12:11 ½s¿è

123.rar (16.06 KB) ³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

¦^´_ 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. For i = 2 To [M65536].End(xlUp).Row '°ê©w°²¤é
  8. temp = Month(Cells(i, 13)) & "," & Day(Cells(i, 13))
  9. ds.Add temp, i
  10. Next
  11. For i = 2 To [O65536].End(xlUp).Row '¸É¤W¯Z
  12. temp = Month(Cells(i, 15)) & "," & Day(Cells(i, 15))
  13. ds1.Add temp, i
  14. Next

  15. For i = [k1] To DateAdd("yyyy", 1, [k1]) - 1
  16. test = Month(i) & "," & Day(i)
  17.    If (Weekday(i, vbMonday) < 6 And ds.Exists(test) = False) Or ds1.Exists(test) = True Then  '¶g¤@¦Ü¶g¤­¨Ã¦©°£MÄæ°ê©w°²¤é¥[¤J¸É¤W¯Z¤é
  18.      s = s + 1
  19.      Arr(s, 1) = i
  20.      Arr(s, 2) = Month(i)
  21.      Arr(s, 3) = Day(i)
  22.      Arr(s, 4) = Weekday(i, 2)
  23.      k = IIf(s Mod UBound(Men) = 0, UBound(Men), s Mod UBound(Men))
  24.      Arr(s, 0) = Men(k, 1)
  25.     End If
  26. Next
  27. [A2].Resize(s, 5) = Arr

  28. Range([E2], [E65536].End(xlUp)).FormulaR1C1 = _
  29. "=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,""¤é"","""")))))))))))))"
  30. Range([E2], [E65536].End(xlUp)).Formula = Range([E2], [E65536].End(xlUp)).Value

  31. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

RE: 100¦~­È¤éªí

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

TOP

¦^´_ 2# Hsieh


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

TOP

  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. Range([E2], [E65536].End(xlUp)).FormulaR1C1 = _
  36. "=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,""¤é"","""")))))))))))))"
  37. Range([E2], [E65536].End(xlUp)).Formula = Range([E2], [E65536].End(xlUp)).Value

  38. End Sub
½Æ»s¥N½X
¦^´_ 4# man65boy
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# Hsieh

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

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD