GBKEE

¦^´_ 1# stevenliu555

½Ð¬d¬Ý³o¸Ìªº °Q½×

1. Sub MainProgram()
   
2. Dim iWi_St_Ret(2 To 241, 1 To 5), iLo_St_Ret(2 To 241, 1 To 5) As Single
   
3. Dim iWi_St_Ret_Perf(2 To 241, 1 To 5), iLo_St_Ret_Perf(2 To 241, 1 To 5) As Single
   
4. Dim i, j, k, n As Integer
   
5. Dim c, r As Integer
   
6. Dim data As Range
   
7. Dim A As Variant      '¥[³oÅÜ¼Æ ****
   
8. 
   
9. iRowNo = Sheets("­ÓªÑ³ø¹S²v").Range("A65536").End(xlUp).Row
   
10. iColumnNo = Sheets("­ÓªÑ³ø¹S²v").Range("HV1").End(xlToLeft).Column
    
11. 
    
12. For j = 2 To iColumnNo
    
13.     For i = 1 To 5
    
14.         iWi_St_Ret_Perf(j, i) = 0
    
15.         iLo_St_Ret_Perf(j, i) = 0
    
16.         iWi_St_Ret(j, i) = Application.WorksheetFunction.Large(Range(Cells(2, j), Cells(iRowNo, j)), i)
    
17.         iLo_St_Ret(j, i) = Application.WorksheetFunction.Small(Range(Cells(2, j), Cells(iRowNo, j)), i)
    
18.         Set data = Range(Cells(j, 2), Cells(100, j + 1))
    
19.         A = Application.VLookup(iWi_St_Ret(j, i), data, 2, False)
    
20.         'A =>¦³§ä¨ì¶Ç¦^¼Æ­È,¤Ï¤§ ¶Ç¦^¿ù»~­È
    
21.         iWi_St_Ret_Perf(j, i) = IIf(Not IsError(A), A, 0)
    
22.         A = Application.VLookup(iLo_St_Ret(j, i), data, 2, False)
    
23.         iLo_St_Ret_Perf(j, i) = IIf(Not IsError(A), A, 0)
    
24.     Next i
    
25. Next j
    
26. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 3# stevenliu555
¤u§@ªíVLookupªº½d³ò­n¥ÎDouble(Âùºë«×¯BÂI¼Æ)
­ì¥»µ{¦¡¤¤ iLo_St_Ret(2 To 241, 1 To 5) As Single(³æºë«×¯BÂI¼Æ) ,¬O©Ò³y¦¨ªº¿ù»~ªº­ì¦]

1. Sub MainProgram()
   
2. 'Dim iWi_St_Ret(2 To 241, 1 To 5), iLo_St_Ret(2 To 241, 1 To 5) As Single  '¥u¦³iLo_St_Ret¦³«ü©w«¬ºA
   
3. 'Dim iWi_St_Ret_Perf(2 To 241, 1 To 5) As Single, iLo_St_Ret_Perf(2 To 241, 1 To 5) As Single  '³£¦³«ü©w«¬ºA
   
4. 
   
5. Dim iWi_St_Ret() As Double  'Âùºë«×¯BÂI¼Æ
   
6. Dim iLo_St_Ret() As Double  '() °ÊºA°}¦C
   
7. Dim iWi_St_Ret_Perf() As Double
   
8. Dim iLo_St_Ret_Perf() As Double
   
9. Dim i, j, k, n As Integer
   
10. Dim c, r As Integer
    
11. Dim data As Range
    
12. iRowNo = Sheets("­ÓªÑ³ø¹S²v").Range("A65536").End(xlUp).Row
    
13. iColumnNo = Sheets("­ÓªÑ³ø¹S²v").Range("HV1").End(xlToLeft).Column
    
14. 'ReDim ³¯­z¦¡  ¦bµ{§Ç¼h¦¸¤¤¥Î¨Ó­«·s°t¸m°ÊºA°}¦CÅܼƪºÀx¦sªÅ¶¡¡C
    
15. ReDim iWi_St_Ret(2 To iColumnNo, 1 To 5)
    
16. ReDim iLo_St_Ret(2 To iColumnNo, 1 To 5)
    
17. ReDim iWi_St_Ret_Perf(2 To iColumnNo, 1 To 5)
    
18. ReDim iLo_St_Ret_Perf(2 To iColumnNo, 1 To 5)
    
19. 
    
20. For j = 2 To iColumnNo
    
21.     For i = 1 To 5
    
22.         'iWi_St_Ret_Perf(j, i) = 0     '¤£¥²¬°0
    
23.         'iLo_St_Ret_Perf(j, i) = 0
    
24.        Debug.Print iWi_St_Ret(j, i)
    
25.         iWi_St_Ret(j, i) = Application.WorksheetFunction.Large(Range(Cells(2, j), Cells(iRowNo, j)), i)
    
26.         iLo_St_Ret(j, i) = Application.WorksheetFunction.Small(Range(Cells(2, j), Cells(iRowNo, j)), i)
    
27.         'Set data = Range(Cells(j, 2), Cells(100, j + 1))  '¥t¤@¿ù»~ÂI Cells(j, 2)=>¤@ª½¬OBÄæ
    
28.         'Set data = Range(Cells(2, j), Cells(100, j + 1))  'Äæ¦ìÀHµÛj
    
29.         Set data = Range(Cells(2, j), Cells(iRowNo, j + 1)) '«Øij¦C¦ì¥Î iRowNo
    
30.         'Debug.Print data.Address             '¥i¬Ý¬Ý½d³ò
    
31.         iWi_St_Ret_Perf(j, i) = Application.VLookup(iWi_St_Ret(j, i), data, 2, False)
    
32.         iLo_St_Ret_Perf(j, i) = Application.VLookup(iLo_St_Ret(j, i), data, 2, False)
    
33.     Next i
    
34. Next j
    
35. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 5# c_c_lai
4#¤w§ó¥¿,¤S±¼ªÛ³Â¤F,·PÁ§iª¾,

GBKEE

¦^´_ 7# stevenliu555

1. Option Explicit
   
2. Sub Ex()
   
3. Dim Ar(1 To 5, 1 To 10)
   
4.     Ar(1, 1) = 1   'Ar(1, 1)µ¥¦P[A1:J5].Cells(1,1)
   
5.     Ar(5, 10) = 50 'Ar(5, 10)µ¥¦P[A1:J5].Cells(5,10)
   
6.     [A1:J5].Cells(1, 1) = Ar(1, 1)
   
7.     [A1:J5].Cells(5, 10) = Ar(5, 10)
   
8.     '**************************
   
9.     Stop
   
10.     Ar(3, 5) = 25
    
11.     [A1:J5].Clear
    
12.     [A1:J5] = Ar
    
13. End Sub

½Æ»s¥N½X