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

[µo°Ý] ÀÉ®×¹L¤j~¨ç¼Æ¦³¨S¦³¿ìªkÂনVBA

[µo°Ý] ÀÉ®×¹L¤j~¨ç¼Æ¦³¨S¦³¿ìªkÂনVBA

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-2-14 06:51 ½s¿è

½Ð°Ý¦U¦ì¤j¤j­Ì
¦¹ªþ¥ó¤¤¨ç¼Æ¦³¨S¦³¿ìªkÂনVBA

¦]¬°¸ê®Æ¤Ó¦h¦pªG¥þ³¡³£¥Î¨ç¼ÆÅã¥ÜÀɮפj¨ì«ÜÃø¶}±Ò
¦³·Q¹L¥Î¥¨¶°¿ý»sªº¤è¦¡
¦ý¬O¨ç¼Æ¨S¿ìªk¿ý»s

¤p§Ì¯uªº«ÜÀY¯k
·Ð½Ð¦³ªÅªº¤j¤j­ÌÀ°À°¤p§ÌÅo

¨ç¼Æ´«¦¨vba.rar (9.51 KB)

¦^´_ 1# gn00487767

¤@Áä·d©w
  1. Private Sub CommandButton2_Click()
  2. Dim Ar()
  3. With Sheets("¦W³æ")
  4.    For Each a In .Range(.[E2], .[E2].End(xlDown)).SpecialCells(xlCellTypeConstants)
  5.       ReDim Preserve Ar(s)
  6.       n = Application.HLookup(a, Array(Array("²Ä¤@Ãþ", "²Ä¤GÃþ", "²Ä¤TÃþ", "²Ä¥|Ãþ", "²Ä¤­Ãþ"), Array(5, 3, 1, 0, 8)), 2, 0)
  7.       y = DateAdd("m", n, a.Offset(, 1))
  8.       Ar(s) = Array(y, n, IIf(Date > y, "¦³®Ä", "µL®Ä"))
  9.       s = s + 1
  10.     Next
  11. End With
  12. ay = Application.Transpose(Application.Transpose(Ar))
  13. With Sheets("ÅçÃÒ")
  14. .Range(.[B2], .[B2].End(xlDown)).ClearContents
  15. .[B2].Resize(s, 1) = Application.Index(ay, , 1)
  16. .Range(.[E2], .[E2].End(xlDown)).ClearContents
  17. .[E2].Resize(s, 1) = Application.Index(ay, , 2)
  18. End With
  19. With Sheets("µ²ªG")
  20. .Range(.[B2], .[B2].End(xlDown)).ClearContents
  21. .[B2].Resize(s, 1) = Application.Index(ay, , 3)
  22. End With
  23. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 1# gn00487767
  1. Sub Ex() '"¦W³æ"¤WFÄ檺¤é´Á¥[¤WEÄæ¤ë¼Æªº¤é´Á
  2.     With Sheets("ÅçÃÒ").[B2:B20]
  3.         .Cells = "=EDATE(¦W³æ!RC6,RC5)"
  4.         '=EDATE(¦W³æ!F2,E2)
  5.         .Cells = .Value '¤½¦¡Âন­È
  6.     End With
  7. End Sub
  8. Sub Ex1()  'ºâ¥X"¦W³æ"¤WEÄ檺¼Æ­È
  9.     With Sheets("ÅçÃÒ").[E2:E20]
  10.         .Cells = "=CHOOSE(MATCH(CLEAN(¦W³æ!RC5),{""²Ä¤@Ãþ"",""²Ä¤GÃþ"",""²Ä¤TÃþ"",""²Ä¥|Ãþ"",""²Ä¤­Ãþ""},0),5,3,1,0,8) " '=EDATE(¦W³æ!F2,E2)
  11.                  '=CHOOSE(MATCH(CLEAN(¦W³æ!$E2),{"²Ä¤@Ãþ","²Ä¤GÃþ","²Ä¤TÃþ","²Ä¥|Ãþ","²Ä¤­Ãþ"},0),5,3,1,0,8)
  12.         .Cells = .Value '¤½¦¡Âন­È
  13.     End With
  14. End Sub
  15. Sub Ex2()  '·í¤Ñªº¤é´Á¤j©ó"ÅçÃÒ"¤WBÄ檺¤é´Áªºµ²ªG
  16.     With Sheets("µ²ªG").[B2:B20]
  17.         .Cells = "=IF(TODAY()>ÅçÃÒ!RC2,""¦³®Ä"",""µL®Ä"")"
  18.         '=IF(TODAY()>ÅçÃÒ!B2,"¦³®Ä","µL®Ä")
  19.         .Cells = .Value '¤½¦¡Âন­È
  20.     End With
  21. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# Hsieh


  ¸U¤À·PÁÂGBKEE¤j¤j
¤p§Ì´ú¸Õµ²ªG,¦¨¥\´î¤ÖÀÉ®×¹L¤jŪ¨ú®É¶¡¤Óªøªº°ÝÃD

TOP

¦^´_ 3# GBKEE

¦A¦¸¸ß°Ý GBKEE ¤j¤j
±z²Ä¤@¦¸µ¹¤p§Ìªº¥N½X
³æ¤@­Ó½Æ»s¨Ï¥Î¥i¦æ
¦ý²Ä¤G¦¸ªº¥N½X
³æ¿W½Æ»s¥N½XµLªk¨Ï¥Î
¦ý¬O¨âªÌ¤@¦P½Æ»s¥N½X¤@¦P¨Ï¥Î«o¥i¥H
¦ý¬Oµ²ªG¦n¹³³£¬O¬Û¦Pªº
¨º¤p§Ì¦pªG­n·s¼W©Î´î¤ÖªF¦è¸Ó¥Î­þ­Ó¤è¦¡
¶i¦æ¨Ï¥Î

TOP

¦^´_ 5# gn00487767
½ÐªþÀÉ»¡©ú§AªººÃ°Ý
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 6# GBKEE

·PÁÂGBKEE¤j¤j
¬O¤p§Ì¦Û¤v½k¶î
­ì¨Ó2ªÌ¬Ò¬Û¦P
¥u¬O²Ä¤G¦¸ªº¥N½X¬Oª½±µÅçºâªº
¤p§Ì¨S°ÝÃD¤F
«áÄò¤p§Ì¦A¦Û¦æ¬ã¨s¬ã¨s
ÁÂÁ¤j¤j

TOP

¦^´_  gn00487767
GBKEE µoªí©ó 2014-2-14 11:04


½Ð±Ð¤j¤j
¤p§Ìµo²{³o¦¸ªº¥N½X¦³»~©O
ÅçÃÒ«áÅã¥Üªº¤é´Á¬OÄA­Ëªº
©Ò¥HÅçÃÒ§¹¥þ¤£¦æ

TOP

¦^´_ 3# GBKEE

¦A¦¸½Ð±ÐGBKEE¤j¤j
¨ä¤¤
08.Sub Ex1()  'ºâ¥X"¦W³æ"¤WEÄ檺¼Æ­È
09.    With Sheets("ÅçÃÒ").[E2:E20]
10.        .Cells = "=CHOOSE(MATCH(CLEAN(¦W³æ!RC5),{""²Ä¤@Ãþ"",""²Ä¤GÃþ"",""²Ä¤TÃþ"",""²Ä¥|Ãþ"",""²Ä¤­Ãþ""},0),5,3,1,0,8) " '=EDATE(¦W³æ!F2,E2)
11.                 '=CHOOSE(MATCH(CLEAN(¦W³æ!$E2),{"²Ä¤@Ãþ","²Ä¤GÃþ","²Ä¤TÃþ","²Ä¥|Ãþ","²Ä¤­Ãþ"},0),5,3,1,0,8)
12.        .Cells = .Value '¤½¦¡Âন­È
13.    End With
¥¦Åã¥Ü¥X¨Óªº¤é´Á¸ò§Úªº¤é´Á®æ¦¡¬OÄA­Ëªº
©Ò¥HÅçÃÒµ²ªG³£¬O¿ù»~ªº
¤p§ÌÀ³¸Ó¦p¦ó°µ­×§ï
·Ð½Ð¤j¤j¶O¤ß

TOP

¦^´_ 3# GBKEE

GBKEE¤j¤j©êºp¤p§Ì¬Ý¿ù¤F
À³¸Ó¬O¤U­±¤~¹ï
        With Sheets("ÅçÃÒ").[B2:B20]
        .Cells = "=EDATE(¦W³æ!F2,ÅçÃÒ!E2)"
        '=EDATE(¦W³æ!F2,E2)
        .Cells = .Value '¤½¦¡Âন­È
    End With
³oÃäÅçÃҫ᪺¤é´Á®æ¦¡¬OÄA­Ëªº

TOP

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD