¦p¦ó¥Îvba¨ú±obomªí¤¤ªº¸ê®Æ

ANGELA

½Ð°Ý¦p¦ó¨ÌÕu²£«~½s¸¹¦Übomªí¤¤¨ú±o¬ÛÃö¸ê®Æ.
¥Ø«e¥Î¨ç¦¡³B²z¦ý«Ü³Â·Ð,»Ý¤@µ§¤@µ§Âà¶K¦¨¼ÆÕu,
½Ðª¾¹Dªº¤j¤j±Ð¾É¤@¤U.

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

ANGELA

¦^´_ 2# Hsieh


     ÁÂÁª©¥Dªº¸Ñµª§Ú·|¸ÕµÛ­×§ï,
   ­è¶i¤Jvba»â°ì,¥ý®ø¤Æ¬Ý¬Ý,¦³¤£À´¦A¨Ó³Â·Ðª©¥D¸Ñµª.

ANGELA

¦^´_ 2# Hsieh


    ¤S¨Ó³Â·Ðª©¥D¤F,¸ÕµÛ­×§ï¤F¤@¤U¦ý¤£²z·Q,½Ðª©¥D¦AÀ°¤U¦£.
    ½Ð¬Ýªþ¥ó.

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

ANGELA

¦^´_ 5# Hsieh
硏¨s¤F«Ü¤[ÁÙ¬O¦³¤£¸Ñªº¦a¤è,¥H¤U¬O§Ú®ø¤Æ¤Î¤£À´ªº¦a¤è½Ð«ü¾É,ÁÂÁÂ
    Sub tt()
Dim Ay(), C As Range, A As Range     '«Å§i AY()¬O°}¦C C ¬O½d³ò  A ¬O½d³ò
Set d = CreateObject("Scripting.Dictionary") '®w¦s
Set ds = CreateObject("Scripting.Dictionary") '¯Á¤Þ
For Each A In Range([E2], [E65536].End(xlUp)) 'A ªº½d³ò¦bSHEET1 ªºE2¨ì³Ì«á¤@µ§¸ê®Æ  ®w¦s
d(UCase(A)) = A.Offset(, 2)  'µ¹D(A)--GÄæ®w¦s¼Æ¶q ?¬O§_­«½Æªº¹s¥ó½s¸¹·|«O¯d³Ì«á¤@­Ó?
Next
With Sheets("¯Á¤Þ")
For Each A In .Range(.[A2], .[A65536].End(xlUp)) '¯Á¤Þ
ds(A.Value) = A.Offset(, 1)   'DS(A) ¨ú±oBOMªíªº¦WºÙ
Next
Ar = [A65536].End(xlUp).Resize(, 4) 'AR «ü©w¬°³Ì«á¤@µ§ªºA:D
With Sheets(ds(Ar(1, 3)))  '³Ì«á¤@µ§CÄæ©Ò¹ïÀ³ªº¤u§@ªí(BOM)
Set A = .Rows(2).Find(Ar(1, 3)).EntireColumn.SpecialCells(xlCellTypeConstants, 1)
'«ü©w A = ²Ä¤G¦C§ä¨ìSHEET1³Ì«á¤@µ§CÄæ¹ïÀ³¨ìªºÄ椤¦³¼Æ­È¸ê®ÆªºÄæ¦ì
For Each C In A
ReDim Preserve Ay(s)  '­«·s«Å§i°}¦CAY(S)¤j¤p
x = .Cells(C.Row, 1).Value '«ü©w X = A°}¦C¤¤¦³­ÈªºAÄ檺¹s¥ó½s¸¹
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)
'¬°¤°»òd(x)·|§ä¨ì³Ì«á¤@µ§®w¦s¸ê®Æ?DICTIONARY¬O¬Û¦P¹s¥ó½s¸¹·|Âл\±¼­ì¨Óªº­È¶Ü?
s = s + 1
Next
End With
End With
[A65536].End(xlUp).Resize(s, 8) = Application.Transpose(Application.Transpose(Ay)) 'Âà¸m
End Sub

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²£¥Í¿ù»~