Hsieh

¦^´_ 1# ANGELA


    ¸Õ¸Õ¬Ý

1. Sub TNT()
   
2. Dim Ar()
   
3. With Sheets(Left([C11], 2))
   
4. Set a = .Rows(2).Find([C11])
   
5. For Each c In a.EntireColumn.SpecialCells(xlCellTypeConstants, 1)
   
6. Set b = [E2:E10].Find(.Cells(c.Row, 1))
   
7. ReDim Preserve Ar(s)
   
8. Ar(s) = Array([A11], [B11], [C11], [D11], .Cells(c.Row, 1), c * [D11], b.Offset(, 2) - c * [D11], Date)
   
9. s = s + 1
   
10. Next
    
11. End With
    
12. [A11].Resize(s, 8) = Application.Transpose(Application.Transpose(Ar))
    
13. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 4# ANGELA

1. Sub TNT()
   
2. Dim Ay(), C As Range, A As Range
   
3. Set d = CreateObject("Scripting.Dictionary") '®w¦s
   
4. Set ds = CreateObject("Scripting.Dictionary") '¯Á¤Þ
   
5. For Each A In Range([E2], [E65536].End(xlUp)) '®w¦s
   
6.    d(UCase(A)) = A.Offset(, 2)
   
7. Next
   
8. With Sheets("¯Á¤Þ")
   
9. For Each A In .Range(.[A2], .[A65536].End(xlUp)) '¯Á¤Þ
   
10.    ds(A.Value) = A.Offset(, 1)
    
11. Next
    
12. Ar = [A65536].End(xlUp).Resize(, 4)
    
13. With Sheets(ds(Ar(1, 3)))
    
14. Set A = .Rows(2).Find(Ar(1, 3)).EntireColumn.SpecialCells(xlCellTypeConstants, 1)
    
15. For Each C In A
    
16. ReDim Preserve Ay(s)
    
17. x = .Cells(C.Row, 1).Value
    
18.    Ay(s) = Array(Ar(1, 1), Ar(1, 2), Ar(1, 3), Ar(1, 4), x, C * Ar(1, 4), d(x) - C * Ar(1, 4), Date)
    
19.    s = s + 1
    
20. Next
    
21. End With
    
22. End With
    
23. [A65536].End(xlUp).Resize(s, 8) = Application.Transpose(Application.Transpose(Ay))
    
24. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 6# ANGELA

dictionaryª«¥ó¡A­Yª½±µµ¹ITEM¤èªk¡A¥L±NÀx¦s¦¨³Ì«á³Q¼g¤Jªº­È
©Ò¥H¡A¹J¨ì­«½ÆªºKEY®É¡A¨Ï¥Î¦¹ªk¤]¯àÁקK¦]¥[¤J¬Û¦PKEY­È®É¡AAdd¤èªk²£¥Í¿ù»~