½Ð±Ð¿z¿ï«á¼Æ­È¥[Á`

jasonwilliams

½Ð°Ý§Ú·Q ¿z¿ï¥X C Äæ¦ì¬Û¦Pªº¸ê®Æ¡]¸ê®Æ¤j¬ù¦³300¦hµ§¡^
¦A§â  D Äæ¦ìªº¼Æ­È¥[Á`
¤§«á¡ACÄæ¦ì¨ä¾l¬Û¦P¸ê®Æ§R°£
¥u¯d¤@µ§¿z¿ï«áªº¸ê®Æ  

­ì©l
     A   B   C   D .  .   .  
1¡@a  x   w ¡@¢´
2   b  x   w ¡@¢³
3¡@c  x    b¡@ ¢´
4¡@d  x   e     6
5¡@e  x   b     7
.
.

µ²ªG
     A   B   C   D .  .   .  
1¡@a  x   w ¡@9
2   c  x    b¡@ 12
3¡@d  x   e     6
4¡@
5¡@
.
.
½Ð°Ý¥ÎVBA ¸Ó«ç»ò¼g¡H

¯¬·s¦~§Ö¼Ö

Hsieh

¦^´_ 1# jasonwilliams

1. Sub Ex()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. For Each a In Range([C1], [C65536].End(xlUp))
   
4.    If d.exists(a.Value) = False Then
   
5.       d(a.Value) = Array(a.Offset(, -2).Value, a.Offset(, -1).Value, a.Value, a.Offset(, 1).Value)
   
6.       Else
   
7.       ar = d(a.Value)
   
8.       ar(3) = ar(3) + a.Offset(, 1).Value
   
9.       d(a.Value) = ar
   
10.     End If
    
11. Next
    
12. Columns("A:D") = ""
    
13. [A1].Resize(d.Count, 4) = Application.Transpose(Application.Transpose(d.items))
    
14. End Sub

½Æ»s¥N½X

jasonwilliams

ª©¥D±z¦n
µ{¦¡½X¥i¥H¨Ï¥Î ·PÁÂ
¡]ÁöµM§Ú¬Ý¤£¤ÓÀ´¡A§Ú·|ºÉ¤O¥h¾Ç²ß¡^:D

¥t¥~§Ú¥Î¤F¤@­Ó «ü¥O«ö¶s·Q°µ¤@Áä²M°£excel ­¶­±¤º®e
§Ú¥Î«Ü²Âªº¤è¦¡°µ¥X¨Óªº
Private Sub CommandButton2_Click()
    Cells.Select
    Selection.Clear
    Range("A1").Select
End Sub
§Ú·QÀ³¸ÓÁÙ¦³§ó¦nªº¤èªk¤~¬O¡H

Hsieh

¦^´_ 3# jasonwilliams

1. Private Sub CommandButton2_Click()
   
2. '³o¸Ì´¡¤J§A¿ý»sªº°Ï¬q
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. For Each a In Range([C1], [C65536].End(xlUp))
   
5.    If d.exists(a.Value) = False Then
   
6.       d(a.Value) = Array(a.Offset(, -2).Value, a.Offset(, -1).Value, a.Value, a.Offset(, 1).Value)
   
7.       Else
   
8.       ar = d(a.Value)
   
9.       ar(3) = ar(3) + a.Offset(, 1).Value
   
10.       d(a.Value) = ar
    
11.     End If
    
12. Next
    
13. Columns("A:D") = ""
    
14. [A1].Resize(d.Count, 4) = Application.Transpose(Application.Transpose(d.items))
    
15. End Sub

½Æ»s¥N½X

²M°£³¡¥÷
Cells.Clear
§Y¥i

jasonwilliams

¤£¦n·N«ä
­è­è¸Õ¤F«Ü¤[
Åý§Ú¸Õ¥X¨Ó¤F

½s¿è§¹¥»¤å
¤~¬Ý¨ìª©¥D¤w¦^¤å


¥t¥~¡G
¦]¬°­ì©l¸ê®Æ¤¤§Ú¥u­n¯S©w´XÄæ¸ê®Æ ¨Ò¦p¡G DÄæ ¨ì EÄæ + ³æ¿W GÄæ
§Úªº°µªk¬O copy ¨ì Sheet2 ¤§«á
¦A§âsheet1 ¨ä¾l¸ê®Æ§R°£
sheet2 ½Æ»s¨ì sheet1
¦A°µ¿z¿ï¥[Á`
¤£ª¾¹D¦³µL¤èªk¥i¥H¤£¥Î¨ì sheet2 ´N¥i¹F¦¨¡H


§Ú¦A¬ã¨sµ{¦¡½X
¤£À´ªº¦a¤è ½Ð±z¦h¦h«ü±Ð
ÁÂÁÂ

jasonwilliams

¤£¦n·N«ä
¤p§Ì¤S¦³·sªº°ÝÃD
¦]¬°¤£·Q®ö¶Oª©¥»
§Ú´Npo¦b³o¤F

½Ð°Ý§Ú·Q°µ¤@­Ó «ü¥O«ö¶s
«ö¤U¥h·|¶K¤W½Æ»sªº¸ê®Æ

Range("A1").PasteSpecial
§Ú¥Î¤W­±ªº»yªk
¨Ï¥ÎªÌ¦pªG¦³½Æ»s¸ê®Æªº¸Ü
¥i¥H¥¿±`¶K¤W

¦pªG¨S½Æ»s¸ê®Æªº¸Ü
·|Åã¥Ü " Class Range ªº PasteSpecial ¤èªk¥¢±Ñ "

½Ð°Ý¬O§Ú¥Î¿ù»yªk
ÁÙ¬O¸Ó«ç»ò¼g¤~¯à§PÂ_¬O§_¦³½Æ»s¸ê®Æ

ÁÂÁÂ

jasonwilliams

¦^´_ 2# Hsieh [code]

  Set D = CreateObject("Scripting.Dictionary")
            For Each a In Range([c1], [c65536].End(xlUp))
            If D.exists(a.Value) = False Then
            D(a.Value) = Array(a.Offset(, -2).Value, a.Offset(, -1).Value, a.Value, a.Offset(, 1).Value)
            Else
            ar = D(a.Value)
            ar(3) = ar(3) + a.Offset(, 1).Value
            D(a.Value) = ar
            End If
            Next

            Columns("A:D") = ""
            [A1].Resize(D.Count, 4) = Application.Transpose(Application.Transpose(D.items))

           

¤£¦n·N«ä  
§Ú²{¦b·Q¿z¿ï B Äæ¦ìªº¸ê®Æ
§Ú§ó§ï ¬õ¦â³¡¥÷ C §ï¦¨ B
«o¥X²{ ÂŦ⨺¦æ¿ù»~

½Ð°Ý§Ú¬O¤Ö§ó§ï­þÃä¤F¡H
¤pªº¤£¤~¡A¬Ý¤£¤ÓÀ´µ{¦¡½X
½Ðª©¥D¦A¦¸¬Û±Ï

ÁÂÁÂ

Hsieh

¦^´_ 7# jasonwilliams


    ½Ð°Ñ¾\VBA»¡©úOFFSET
·í¦bBÄæ§@Åܼưj°é®Éa.offset(,-2)´N·|²£¥Í¿ù»~