jcchiang

¦^´_ 2# Hsieh
ÁÂÁª©¤j«ü¾É,¦³´X­Ó°ÝÃD½Ð±Ð¤@¤U
1.«È¤á§å¸¹³£Åã¥Ü"A120004"

¤U¸ü (57.14 KB)

2013-12-9 17:41

2.ÁÙ¦³¤@ºØ±¡§Î¬O¸Ó«È¤á§å¸¹µLTCode®ÉB1~B4´NÅã¥ÜN/A
©Î¬O¦³TCode¦ý¦bSheet1§ä¤£¨ì¦¹TCode,B1~B4´NÅã¥ÜN/A

Hsieh

¦^´_ 1# jcchiang

1. Sub ex()
   
2. Dim Ar(0 To 7), A As Range, C As Range, TCode, Cnt
   
3. Ar(0) = Array("«È¤á¥N¸¹", "«È¤á§å¸¹", "Icode", "¿é¤J¶q", "TCode", "B1", "B1¥¢±Ñ", "B2", "B2¥¢±Ñ", "B3", "B3¥¢±Ñ", "B4", "B4¥¢±Ñ")
   
4. Set A = Sheet2.[F:F].Find("A120004", lookat:=xlWhole)
   
5. If Not A Is Nothing Then
   
6.    For i = -3 To 3
   
7.    TCode = A.Offset(i, 7).Value
   
8.    Set C = Sheet1.[D:D].Find(TCode, lookat:=xlWhole)
   
9.    b1 = C.Offset(, 4).Value
   
10.    b2 = C.Offset(, 6).Value
    
11.    b3 = C.Offset(, 8).Value
    
12.    b4 = C.Offset(, 10).Value
    
13.    Cnt = A.Offset(i, 4).Value
    
14.       Ar(i + 4) = Array(A.Offset(i, -5).Value, A.Value, A.Offset(i, 2).Value, Cnt, TCode, b1, b1 / Cnt, b2, b2 / Cnt, b3, b3 / Cnt, b4, b4 / Cnt)
    
15.    Next
    
16. End If
    
17. With Sheet3
    
18. .[A:M].ClearContents
    
19. For i = 7 To 13
    
20. .Columns(i).NumberFormat = "0.00%"
    
21. Next
    
22. .[A1].Resize(8, 13) = Application.Transpose(Application.Transpose(Ar))
    
23. End With
    
24. End Sub

½Æ»s¥N½X