¨Ï¥ÎVBA¨D¸ê®Æ²Î­p¿é¥X

b9208

¦U¦ì¥ý¶i«e½ú
½Ð±Ð¦p¦ó³]­pVBAµ{¦¡¨Ó°õ¦æ¡C
­pºâ¡ª¸ê®Æªí¡ª¤º¦a°Ï¤Î©m¦W¬Û¦P¤§¦¸¼Æ¡A¨Ã¿é¥X©ó¡ª²Î­pªí¡ª¤¤¡C
«D±`·PÁ¨ó§U
H1.rar (3.11 KB)

¤U¸ü¦¸¼Æ: 14

2011-2-24 00:07

hugh0620

¦^´_ 1# b9208


    ¼g±o«ÜÄê~ ¦ýÁÙ¬O¥i¥H¨Ì§A³]ªº±ø¥ó§¹¦¨~
    ª©¥D¤j¤j·|¦³§ó¦nªº¼gªk  §Úªº¼gªk¬Oªì¾ÇªÌªº¼gªk­ò~ ¶È¨Ñ°Ñ¦Ò

    H1_²Î­p.rar (15.03 KB)

¤U¸ü¦¸¼Æ: 11

2011-2-24 09:41

b9208

¦^´_ 2# hugh0620

H2.rar (14.83 KB)

¤U¸ü¦¸¼Æ: 5

2011-2-24 10:50

ÁÂÁ«e½ú«ü¾É
¥Ø«e´ú¸ÕOK
¥t½Ð±Ð¦p¤U±ø¥ó¡G
¤@¡B¡i¿é¥X¤p­p¡j¡G¥u¿é¥X¥x¥_¡B«ÌªF¡B·s¦Ë¦a°Ï (D3:F3)
¤G¡B¡i¿é¥X¦X­p¡j¡G¿é¥X©Ò¦³¦a°Ï
½Ð°Ñ¾\²Î­pªí
Thanks

Hsieh

¦^´_ 3# b9208
ªþ¥ó¤º¼Ï¯Ã¤ÀªRªí¤èªk¨Ñ§A°Ñ¦Ò H1_²Î­p.rar (16.29 KB)

¤U¸ü¦¸¼Æ: 29

2011-2-24 12:02

1. Private Sub CommandButton1_Click()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. Set d1 = CreateObject("Scripting.Dictionary")
   
4. Set d2 = CreateObject("Scripting.Dictionary")
   
5. With Sheet3
   
6.    For Each a In .Range(.[B6], .[B65536].End(xlUp))
   
7.       ar = Array(a, a.Offset(, 2), a.Offset(, 3), "")
   
8.       d(a.Offset(, 3).Value) = d(a.Offset(, 3).Value) + 1
   
9.       ar(3) = d(a.Offset(, 3).Value)
   
10.       d1(a.Offset(, 3).Value) = ar
    
11.       d2(a.Value) = d2(a.Value) + 1
    
12.     Next
    
13. End With
    
14. With Sheets("²Î­pªí")
    
15. .[B7:F65536] = ""
    
16. .[B7].Resize(d1.Count, 4) = Application.Transpose(Application.Transpose(d1.items))
    
17. .[B6].Resize(d1.Count + 1, 4).Sort key1:=.[B7], key2:=.[C7], header:=xlYes
    
18. .[G7:H65536] = ""
    
19. .[G7].Resize(d2.Count, 1) = Application.Transpose(d2.keys)
    
20. .[H7].Resize(d2.Count, 1) = Application.Transpose(d2.items)
    
21. .[G6].Resize(d2.Count + 1, 2).Sort key1:=.[G7], header:=xlYes
    
22. .Select
    
23. End With
    
24. End Sub

½Æ»s¥N½X

b9208

¦^´_ 4# Hsieh
ÁÂÁª©¥D
µ{¦¡´ú¸Õ¥i¥H
¼Ï¯Ã¤ÀªRªí¤èªk¥Ø«e¾Ç²ß¤¤

aa7551

ª©¥D,½Ð±ÐWith Sheet3    For Each a In .Range(.[B6], .[B65536].End(xlUp))..... End With
sheetªº¶¶§ÇÀ³¬°:²Î­pªí,¸ê®Æªí,¼Ï¯Ã¤ÀªRªí,¦b¦¹®É¨Ï¥Îsheet3,¤£¬OÀ³¬°¼Ï¯Ã¤ÀªRªí¶Ü?½Ð¸Ñµª°g¬z

b9208

¦^´_ 6# aa7551

½Ð¶i¤J¡ªµ{¦¡À˵ø½X¡ª¬d¬Ý
sheet2 (²Î­pªí)
sheet3 (¸ê®Æªí)
sheet 4 (¼Ï¯Ã¤ÀªRªí)
¤£¤@©w²Ä¤@­Ó¤u§@ªí´N¬Osheet1

Hsieh

¦^´_ 6# aa7551


    Sheet3¬O¸ê®ÆªíªºCodeName

¤U¸ü (10.01 KB)

2011-2-26 18:32

aa7551

¾Ç²ß¤F,¥H«eÁ`»{¬°¬O¤u§@ªíªº±Æ¦C¶¶§Ç¬°¥D,¨ü±Ð¤F,ÁÂÁÂ

GBKEE

¦^´_ 6# aa7551

1. Sub Ex()
   
2. Dim i As Integer
   
3. For i = 1 To Sheets.Count
   
4.     MsgBox Sheets(i).Name
   
5. Next
   
6. End Sub

½Æ»s¥N½X

¦p¹Ï:

¤U¸ü (9.52 KB)

2011-2-27 14:49