[µo°Ý] ½Ð°Ý¤@­Ó¥¨¶°³]©wªº°ÝÃD

lone_tiger0303

§Ú·Q­n¤@¶}±ÒexcelÀÉ´N¯à¦Û°Ê°õ¦æªþ¥ó»Ý¨D
½Ð°Ý­n¦p¦ó³]­p¥¨¶°µ{¦¡½X
ÁÂÁÂ

¤U¸ü (62.61 KB)

2010-5-24 12:48

Hsieh

¨ä¹ê¥Î¤½¦¡´N¯à¸Ñ¨M
­n¥Îvbaªº¸Ü

1. Private Sub Workbook_Open()
   
2. Dim Ay(3, 2)
   
3. Ay(0, 0) = 0: Ay(0, 1) = "©¾¸Û«È": Ay(1, 0) = 9.1: Ay(1, 1) = "¤[¥¼¦^«È": Ay(2, 0) = 12.1: Ay(2, 1) = "¬y¥¢«È"
   
4. With Sheet1
   
5.    Set rng = .Range(.[E2], .[E65536].End(xlUp))
   
6.       For Each a In rng
   
7.          If IsDate(a) Then
   
8.          m = Round((Date - a) / 30, 2)
   
9.          k = Application.VLookup(m, Ay, 2)
   
10.          a.Offset(, 11).Resize(, 3) = Array(m, k, a.Offset(, -3) & "-" & k)
    
11.          End If
    
12.        Next
    
13. End With
    
14. End Sub

½Æ»s¥N½X

lone_tiger0303

¦]¬°¸ê®Æ§¨¦³¤W¸Uµ§¡A©Ò¥H·Q¥Î¥¨¶°°õ¦æ¡A¥H´î¤Ö®É¶¡®ö¶O
¥t¥~ÁÙ¦³¨Ç°ÝÃD¦A¦V½Ð±Ð±z¤@¤U~~¦p°ÆÀÉ
Áٽбz¨ó§U¡AÁÂÁÂ

Hsieh

¦^´_ 3# lone_tiger0303


    ½Ð¤W¶Ç§AªºxlsÀÉ®×
§OÅý¤H®a¦^ÂЧAªº°ÝÃD,ÁÙ­n¦A°µ¤@­ÓÀÉ®×´ú¸Õ

lone_tiger0303

¤j¤H­ÞªPªü~~~
¤p§Ì«ö¤W¶Çªþ¥ó®É¦n¹³¥u±µ¨ü¹ÏÀÉ­C
ÁÙ¬O§Ú­þ¸Ì¾Þ§@¿ù»~

Hsieh

A,YµL¹ïÀ³®É¦p¦ó¨ú­È?
S¤é´Á«ç¼Ë¨ú±o?
½Ð»¡©úÀÉ®×±ý¾Þ§@¬yµ{¬°¦ó

lone_tiger0303

Àɮ׻ݨD¡G
1.A~OÄ檺¸ê®Æ¡ASÄæ¸ê®Æ»PY~ZÄæ¦ì¸ê®Æ·|¨C¤ë¤â°Ê§ó·s
2.EÄæ¦ìªº¤é´Á·|¦]AÄæ¦ì»PYÄæ¦ì¤ñ¹ï«á¡A±N¬Û²ÅªºZÄæ¸ê®Æ±a¨ìEÄæ¦ì¡AÁ|¨Ò¡G
A           E                                             Y     Z   
¶µ¥Ø      ¤é´Á                                                      ¶µ¥Ø   ¤é´Á
AB     2009/5/8                                  AB    2010/1/1

¦]AÄæAB¨µYÄæ§ä¨ì¬Û²Å¶µ¥ØAB¡A©ó¬OEÄæ¤é´Á·|¥Ñ2009/5/8¡÷2010/1/1

Hsieh

¦^´_ 7# lone_tiger0303

1. Private Sub CommandButton1_Click()
   
2. Dim Ay(3, 2), My As Range, A As Range
   
3. Ay(0, 0) = 0: Ay(0, 1) = "©¾¸Û«È": Ay(1, 0) = 9.1: Ay(1, 1) = "¤[¥¼¦^«È": Ay(2, 0) = 12.1: Ay(2, 1) = "¬y¥¢«È"
   
4. With Sheet1
   
5. For Each A In .Range(.[A2], .[A65536].End(xlUp))
   
6.    Set My = .Columns("Y").Find(A, lookat:=xlWhole)
   
7.    If Not My Is Nothing Then A.Offset(, 4) = My.Offset(, 1)
   
8.    If A.Offset(, 18) < A.Offset(, 4) Then A.Offset(, 18) = ""
   
9.    If A.Offset(, 18) > A.Offset(, 4) And A.Offset(, 19) = "" Then A.Offset(, 19) = DateAdd("m", 3, A.Offset(, 18))
   
10.    If Date > A.Offset(, 19) Then A.Offset(, 18).Resize(, 2) = ""
    
11. Next
    
12.    Set rng = .Range(.[E2], .[E65536].End(xlUp))
    
13.        For Each A In rng
    
14.          If IsDate(A) Then
    
15.          m = Application.Max(0, Round((Date - A) / 30, 2))
    
16.          k = Application.VLookup(m, Ay, 2)
    
17.          A.Offset(, 11).Resize(, 3) = Array(m, k, A.Offset(, -3) & "-" & k)
    
18.          End If
    
19.        Next
    
20. End With
    
21. End Sub

½Æ»s¥N½X

lone_tiger0303

·PÁ±zªº¨ó§U~~
«áÄò¦³°ÝÃD¦A¦V±z½Ð±Ð

lone_tiger0303

If Date > A.Offset(, 19) Then A.Offset(, 18).Resize(, 2) = ""

½Ð°Ý³o±øµ{¦¡½Xªº·N«ä¬°¦ó©O