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

[µo°Ý] ¼g¤@­Ó¤Ñ¼Æ²Ö¥[ªº¦Û­q¨ç¼Æ

[µo°Ý] ¼g¤@­Ó¤Ñ¼Æ²Ö¥[ªº¦Û­q¨ç¼Æ

¤j®a¦n~~~¦b¸£³U¥´µ²®É¡A¥i¥H§ä¨ì³o­Ó°Q½×ª©¡A¹ê¦bı±o¤Ó¦³©¯¤F!!
±ý³Â·Ð¦U¦ì¥ý¶i­Ì¸Ñ±Ï«üÂI

·Q¼g¤@­Ó¨ç¼Æ¡A¥Î¨Ó­pºâ¤Ñ¼Æªº²Ö¥[
¥\¯à¤j·§¬O¡G
¦bBÄæ¦ì¦³¤U©Ô¿ï³æ¡A¨ä­È¦³PD, CL
­Y¿ï³æ¤U©Ô¿ïPD®É¡A¨äCÄæ¦ì·|¶}©l­pºâpending day (¦¹¦ì¸m¤]¬O±ý©ñ¨ç¼Æ§PÂ_ªº¦ì¸m)
¿ï³æ¤U©Ô¿ïCL®É¡A¨äCÄæ·|°±¤î²Ö¥[¡A
¦ý¬O¤U¦¸¿ï³æ¦A¿ïPD®É¡ACÄæ¤S»Ý¶}©l­pºâ²Ö¥[¡C

¨Ò¦p¡G
11/20 ¿ïPD®É¡ACÄæ­p­È 1¡A¹L¨â¤Ñ11/22¥´¶}Excel¡ACÄ檺­p­È·|¦Û°Ê¦¨ 3¡A¨Ã¥B¤]¦b³o¤Ñ±N¿ï³æ¤Á´«CL¡C
11/24¦A¥´¶}Excel¡A±N¿ï³æ§ï¬°PD¡AÃö³¬ÀɮסC
11/25¥´¶}¡A¨äCÄ檺­p­È·|¦Û°Ê¦¨ 4¡C

¥Ø«e³o­Ó¥\¯à¡A§Ú¤À¦¨«Ü¦h¦a¤è¨Ó³B²z@@
·Ð½Ð¨£ªþ¥ó ­pºâpending day.zip (10.22 KB) ºt¥Ü¡A¥H¤U¬°§Úªº§@ªk²³æ»¡©ú¡G
1. ¦bC Ä榳IF¨ç¼Æ§PÂ_¡GB Ä欰PD®É¡A­È¬°1
2. ¦bD Äæ¤]¦³IF¨ç¼Æ§PÂ_¡GC Äæ­È¬°1®É¡A±a¤J¤µ¤Ñªº¤é´Á
3. ¼gVBA¦bExcelÃö³¬®É¡A§PÂ_D, C Äæ¬Ò¬°true®É¡A´Ncopy D Äæ­È¦ÜEÄæ
4. ¤@¼Ë¼gVBA¦bExcelÃö³¬®É¡A·|§ó·sE Ä檺¤é´Á¬°¤µ¤Ñªº¤é´Á
5. ¼gVBA¦b¦bExcel¥´¶}®É¡A§PÂ_¤µ¤Ñ¤é´Á»PE Ä檺¤é´Á¤£¦P¡A«h°µ¬Û´î­pºâ°Ê§@¡A±a¤J­ÈC Äæ¥h

§Úı±o§Ú¥Ø«eªº§@ªk·d±o¤Ó½ÆÂø¦h¾l......
©Ò¥H¤@ª½«Ü·Q²¤Æ¦¨¨ç¼Æ¨Ó¶]¡A¤£ª¾¹D¬O§_¥i¦æ!? How??
¦A³Â·Ð¦U¦ì¥ý¶i­Ì«ü±Ð¤@µf¡A·PÁ¡I

¦^´_ 1# naraohya
¥Î¤u§@ªíªº¹w³]¨Æ¥ó ±N¤é´Á¸m©óÀɮ׸ոÕ
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     If Target(1).Cells = Range("B3") Then
  3.         If Target = "PD" Then
  4.             If [iv1] = "" Then
  5.                 [c3].Resize(98) = 1
  6.             Else
  7.                 [c3].Resize(98) = [c3] + Date - [iv1]
  8.             End If
  9.             [iv1] = Date
  10.         End If
  11.     End If
  12. End SubEnd Sub
½Æ»s¥N½X

TOP

¦^´_ 2# GBKEE


¥ýÁÂÁÂGBKEE¤jªº¦^ÂСI±zªº¼gªk¦³°÷²©úªº¡Aª½¬O¥Ì«ô¤U­·....
§Ú¤£¤Ó¤F¸Ñ¡u±N¤é´Á¸m©óÀɮסv¡A­n©ñ¦b­þ­ÓÄæ¦ì¤W¡H
¯à§_µy·L¸Ô²Ó¸Ñ´b»¡©ú¤@¤U¡AÁÂÁ¡I

TOP

¦^´_ 3# naraohya
§Ú©ñ¦b IV1 [¤u§@ªí³Ì¥k°¼] ©ÎªÌ§A¥i¥H¸m©ó§A¥Î¤£¨ìªº¦a¤è ±N¤é´Á¬ö¿ý¤U¨Ó

TOP

¦^´_ 4# GBKEE


¤£¦n·N«ä¡AGBKEE¤j¡A¤pªºÁÙ¬O¥¼¸Õ¥X¨Ó....
¦³½T©w±Nµ{¦¡½X©ñ¦bWorksheet_Change¨Æ¥óµ{§Ç¸Ì¡A
¥h¤Á´«BÄæ¿ï³æ¬°PD®É³£¥¼¦³°ÊÀR@@
1. ¤£¤Ó©ú¥ÕResize(98)¦b¦¹¬O«ü°µ¤F¤°»ò°Ê§@?
2. ³o¬qµ{¦¡¦ü¥G¥u¦³¦b¦P¤@¦C¡A¦bB3, C3¤~¥i¹B¦æ!?
¦ý§Ú»Ý­n­YBÄ榳³]©wPD®É¡A¹j¾ÀªºCÄæ´N·|¶}©l­pºâ²Ö¥[......
¦A³Â·Ð´£ÂI¡A·PÁ¡I

TOP

¦^´_ 5# naraohya
1. ¤£¤Ó©ú¥ÕResize(98)¦b¦¹¬O«ü°µ¤F¤°»ò°Ê§@?
§AªºªþÀÉ ¬O«ü Range("B3") ¦³ "PD" ¦r¦ê®É -> [c3].Resize(98)=­pºâ¤é¼Æ  
[c3].Resize(98)-> §tc3ÂX¥R98¦C =c3:c100ªº½d³ò
2. ³o¬qµ{¦¡¦ü¥G¥u¦³¦b¦P¤@¦C¡A¦bB3, C3¤~¥i¹B¦æ!?
½ÐªþÀɬݬÝ

TOP

¦^´_ 1# naraohya


¤£ª¾¹D¦³¨S¦³²z¸Ñ¿ù»~
ª½±µÅýDÄæ°O¦í²Ä¤@¦¸BÄæ¿ïPD®Éªº¤é´Á(³o¥ÎCHANGE¨Æ¥ó¨Ó¿é¤JC¡ADÄ檺­È)
Sheet3¼Ò²Õ
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. If Target.Count > 1 Then Exit Sub
  3. If Intersect(Target, [B3:B100]) Is Nothing Or Target = "" Then Exit Sub
  4. Application.EnableEvents = False
  5. If Target.Offset(, 1) = "" Then Target.Offset(, 1).Resize(, 2) = Array(IIf(Target = "PD", 1, ""), IIf(Target = "PD", Date, ""))
  6. Application.EnableEvents = True
  7. End Sub
½Æ»s¥N½X
¨C¦¸¶}ÀɮɭpºâDÄæ¤é´Á¨ì·í¤éªº¤Ñ¼Æ¨Ã§ó§ïDÄ欰·í¤é¤é´Á
  1. Private Sub Workbook_Open()
  2. Dim A As Range
  3. With Sheet3
  4. Application.EnableEvents = False
  5. For Each A In .[B3:B100]
  6.    If A = "PD" And IsDate(A.Offset(, 2)) Then _
  7.    A.Offset(, 1) = _
  8.    DateDiff("d", A.Offset(, 2), Date) + A.Offset(, 1): A.Value = "CL": A.Offset(, 2) = Date
  9. Next
  10. Application.EnableEvents = True
  11. End With
  12. End Sub
½Æ»s¥N½X
­pºâpending day.zip (11.84 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# GBKEE


ÁÂÁÂGBKEE¤j¤£¹½¨ä·Ðªº¦^ÂСAÀɮצ³¸Õ¥X¨Ó
§Ú©ñ¿ù¨ì¤u§@ï¨Æ¥ó¥h¤F.....(´~­¸)
¤£¹L¸Õ¥X«á¡A¸ò§Ú­nªº¹B§@¬O¤£¤Ó¤@¼Ë¡A¥H¤U¡G
1. BÄæ¤@¿ïPD®É, ¨äCÄ檺pending day¨Ã¨S¦³¥ý²Ö­p­È1¡A¦Ó¬O0
2. §e¤W¡A¨Ò¡G¦bB3Äæ¿ï­ÈPD®É¡A´Á±æ¬O¶È®ÇÃ䪺C3Ä榳­È¡A¦Ó«D¾ã±ÆªºCÄæ³£¦³­È¡C
3. ¹j¤Ñ¥´¶}Excel¡A¨äpending day¥¼¦Û°Ê§ó·s¨ä¤Ñ¼Æ¡A¬O¦A­«·sÂI¿ïBÄæ¿ï¾ÜPD¡Apending day¤~§ó·s¡C

Á`¤§¡A«D±`ÁÂÁ¡A´£¨Ñ¤F§Ú¥t¤@ºØVBAªº¾Ç²ß¼gªk¡I

TOP

¦^´_ 7# Hsieh


ÁÂÁÂHsieh¤jªº¦^ÂСA§Ú¥ý¸Õ¤F§@ªk¡A©ú¤Ñ¦³®É¶¡¦A¨Ó¦n¦n®ø¤Æµ{¦¡½X XD (§Ú¹ï offsetªº¥Îªk¦Ñ¬O·P¨ìÀY©ü@@)
¦³µo²{¤@­Ó¤p¿ù»~¡G
²Ä¤G½gµ{¦¡½X²Ä8¦C :A.Value="CL" ¦n¹³À³¸Ó³]"PD"

¥t¥~¡A¥u¦³¸Ó¦CBÄæ­È¬°PD¡A¨äDÄæ¤~§ó·s¤é´Á¡A¦ýBÄæ­È¬°CLªº«o¥¼§ó·s¡A
³o¼Ë¹ï©ó¤§«áBÄ檺CL¤S§ï¬°PD®É¡A¨äpending dayºâ¥X­È¦bÅÞ¿è¤W¬O¤£¹ïªº¡C

¨â¦ì¤j¤jªº¼gªk¡A³£¦³µ¹§Ú·s±Òµo¡A´Á¬ß§Ú¥i¥H¸I¼²¥Xº¡·Nªº§@ªk!! ¦³ªº¸Ü·|¦A¨Ó¤À¨É­ò~~§Æ±æ!!

TOP

        ÀR«ä¦Û¦b : ¡i¬°µ½Ävª§¡j¤H¥Í­n¬°µ½Ävª§¡A¤À¬í¥²ª§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD