[µo°Ý] °}¦C¿z¿ï°ÝÃD(¤w¸Ñ¨M)

softsadwind

½Ð±Ð¦U¦ì¥ý¶i
¦p·~°È¤é³øªí¦p¤U

¤U¸ü (65.33 KB)

2011-8-25 11:57

¥Î¤½¦¡¿z¿ï¥X¤£­«½Æªº«È¤á¦WºÙ
'=T(INDEX('2011'!B:B,MIN(IF(COUNTIF(B$1:B1,'2011'!$B$2:$B$1536),65536,ROW('2011'!$B$2:$B$1536)))))

¤U¸ü (58.52 KB)

2011-8-25 11:57

¦A¥Î=OFFSET('2011'!A$1,MATCH(«È¤á¤@Äý!B2,'2011'!$B:$B,0)-1,0,1,6)
¥h§P§O¸Ó«È¤á³Ì«á¤@¦¸Ápôªº®É¶¡

¤U¸ü (39.64 KB)

2011-8-25 11:57

µM«á¥Î°}¦C¥h§P§O n¤Ñ«e ³Ì«á¤@¦¸Ápô
'=IF(³Ìªñ¤@¦¸Ápô!$A$2:$A$600<(TODAY()-B1),³Ìªñ¤@¦¸Ápô!A2:F600,"")

¤U¸ü (46.66 KB)

2011-8-25 11:57

ªþ¥ó ¤u§@¤é³øªía.rar (11 KB)

¤U¸ü¦¸¼Æ: 17

2011-8-25 11:57

¦]¬°³o¼Ë¤l°µ ¦pªG¶W¹L2000¦æ¤§«á¡A¥u­n¦³¤@¹j¸ê®ÆÅܧó¡A´N·|­«·s­pºâ¤@¦¸¡A¹q¸£´N¤@ª½¹y¡C§Ú¥u¯à¥ÎÀx¦s­Èªº¤è¦¡¡A¥h´î¤Öµ{¦¡§PÂ_ªº¦¸¼Æ¡A©ÎªÌ§â«á­±¨â­Ó¤À­¶²¾¨ì¥t¥~¤@¦UÀɮסA¦³»Ý­n¦A¥´¶}¡A¤~¯à´î¤Ö¤@ª½§PÂ_ªº°ÝÃD¡C¤£ª¾¹D¦³¨S¦³°ª¤â¥i¥H«ü¾É¤@¤U¡A«ç¼Ë¥Îvba¨Ó¼g¡C·P¿E¤£ºÉ¡C

GBKEE

¦^´_ 13# softsadwind
§Aªº»yªk·§©À¨S¦³«Ø¥ß
Sheets(2011)     ->¦b³o¬¡­¶Ã¯¸Ìªº²Ä2011­Ó¤u§@ªí  2011¬O¼Æ¦r  
Sheets("2011") -> ­Ó¤u§@ªí¦WºÙ "2011"¬O¦r¦ê
Sheet1.Sheet2.Sheet3.... ¬O¤u§@ªíªºCodeName -> VBA¤¤¤u§@ªíªºª«¥ó¦WºÙ

softsadwind

¦^´_ 11# GBKEE

禀§i¨â¦ì¤j¤j....¤p§Ì´ú¸Õ¦p¤U
¥ÎOffice 2003¥Dµ{¦¡¶}±Ò
­×§ï¦WºÙ
¤u§@ªí1 ¡G¦¹³B»Ý­nª«¥ó
sheet 1 ¡G«¬ºA¤£²Å¦X
sheet1(2011) ¡Gª«¥ó¤£¤ä´©¦¹ÄݩʩΤèªk

¥Î2010µ{¦¡¶}±Ò
³£¬O¥X²{¥H¤U¿ù»~°T®§

¨t²Î¿ù»~&H800700E(-2147024770) §ä¤£¨ì«ü©wªº¼Ò²Õ
½sĶ¿ù»~¡G Withª«¥óªº§ÎºA¥²¶·¬O¨Ï¥ÎªÌ¦Û­q«¬ºA¡BObject©ÎVariant¤§¤@

©Ò¥HÀ³¸Ó¬O§Ú¦w¸Ëªº2010¦³°ÝÃD...¤]´N¬O.....(®ø­µ)
¦]¬°¬O¤½¥q¹q¸£ ¤£®e©ö°µ¥ô¦óÅܧó¡A©Ò¥H...µL­­´Á¼È½w...

Hsieh

¦^´_ 10# softsadwind

§Aªºªþ¥ó¤¤ÀÉ®×À³¬O2010ª©¦s¦¨97-2003ª©
©Ò¥H¤u§@ªíªºCodeName¬O"¤u§@ªí1"ªº¦r¦ê
±Nµ{¦¡½X¤º³o¨Ç§ï¦¨¥¿½T¤u§@ªí¦WºÙ¸Õ¸Õ

GBKEE

¦^´_ 10# softsadwind
¤£¬O±À´úÀ³¸Ó¬O2010ªº»yªk

With ¤u§@ªí1
With ¤u§@ªí2
With ¤u§@ªí3
­×§ï¬õ¦r³¡¤Àª«¥ó¦WºÙ²Å¦X§Aªº¤u§@ªíª«¥ó¦WºÙ¨Ò¦p" Sheet1,Sheet2,Sheet3...

softsadwind

¦^´_ 8# Hsieh

·PÁ¡A "ÁÂ"¤j¤j¡A§Ú¦b2003¨Ï¥Î¦¹µ{¦¡½X¡A¥X²{¡y¨S¦³ª«¥ó¡z¡A±À´úÀ³¸Ó¬O2010ªº»yªk¡C©Ò¥H¼È®É¨S¿ìªk¦^À³³o¤@½g¡C

softsadwind

¦^´_ 6# GBKEE

·PÁª©¤j¡A³o¤@ª©´N°÷¥Î¤F...
´ú¸Õ°_¨Ó¬Ookªº...
¦p¤£¤¶·N §Ú·|§â³o­Ó½d¨Ò©ñ¦b­Ó¤HÁy®Ñ¤W
²¦³º°Ý¨Óªº ¤ñ¸û®e©ö§Ñ°O
±o§ä­Ó¦a¤è¼Ð°O°_¨Ó:>

Hsieh

¦^´_ 7# softsadwind
·ÓµÛ§Aªº¤½¦¡·N¸q¡A§ï¦¨¨Ï¥ÎVBA±Ô­z

1. Sub ex()
   
2. Dim Ar()
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. Set d1 = CreateObject("Scripting.Dictionary")
   
5. With ¤u§@ªí1
   
6.    For Each a In .Range(.[B2], .[B65536].End(xlUp))
   
7.       d(a.Value) = a.Offset(, -1).Value
   
8.       d1(a.Value) = a.Offset(, -1).Resize(, 6).Value
   
9.    Next
   
10. End With
    
11. With ¤u§@ªí2
    
12. .[B2:B65536] = ""
    
13. .[B2].Resize(d.Count, 1) = Application.Transpose(d.keys)
    
14. End With
    
15. With ¤u§@ªí3
    
16. .[A2:F65536] = ""
    
17. .[A2].Resize(d1.Count, 6) = Application.Transpose(Application.Transpose(d1.items))
    
18. End With
    
19. With ¤u§@ªí4
    
20. n = .[B1]
    
21. For Each ky In d.keys
    
22. If Date - n >= d(ky) Then
    
23. ReDim Preserve Ar(s)
    
24. Ar(s) = d1(ky)
    
25. s = s + 1
    
26. End If
    
27. Next
    
28. .[A3:F65536] = ""
    
29. .[A3].Resize(s, 6) = Application.Transpose(Application.Transpose(Ar))
    
30. End With
    
31. End Sub

½Æ»s¥N½X

softsadwind

¦^´_ 6# GBKEE


    ÁÂÁ §Ú¦^¤½¥q¦A¨Ó´ú¸Õ¡A´ú¸Õ«á¦A¦^õX

GBKEE

¦^´_ 5# softsadwind
µM«á¦A¥Î½Æ»sªº¤è¦¡ ±µÄò2011­¶­±ªº³Ì«á¤@¦æ,2011¨º¤@­¶´Nºû«ù­ì»ª¡C  ½Ð§A¨Ó§¹¦¨

1. Sub Ex()
   
2.     Dim D As Object, Rng As Range, E As Variant
   
3.     Set D = CreateObject("SCRIPTING.DICTIONARY")
   
4.     Set Rng = Sheets("2011").[A2]
   
5.     Do While Rng <> ""
   
6.         If D.exists(Rng.Cells(1, 2).Value) = 0 Then
   
7.             Set D(Rng.Cells(1, 2).Value) = Rng.Resize(, 6)
   
8.         Else
   
9.         If D(Rng.Cells(1, 2).Value).Cells(1) < Rng Then Set D(Rng.Cells(1, 2).Value) = Rng.Resize(, 6)
   
10.         End If
    
11.         Set Rng = Rng.Offset(1)
    
12.     Loop
    
13.     Sheets("³Ìªñ¤@¦¸Ápô").UsedRange.Offset(1).Clear
    
14.     Sheets("Last - N").UsedRange.Offset(2).Clear
    
15.     For Each E In D.Items
    
16.         If Date >= DateAdd("M", 1, E.Cells(1)) Then E.Copy Sheets("Last - N").Range("A" & Rows.Count).End(xlUp).Offset(1)
    
17.         E.Copy Sheets("³Ìªñ¤@¦¸Ápô").Range("A" & Rows.Count).End(xlUp).Offset(1)
    
18.     Next
    
19.     Set D = Nothing
    
20.     Set Rng = Nothing
    
21. End Sub

½Æ»s¥N½X