[µo°Ý] ¬°¤°»òVLOOKUP¤£¯à°õ¦æ¡H

stevenliu555

½Ð°Ý¡G

¬°¤°»òªþ¥[Àɮפ§¤U¦CVBA½X¤£¯à°õ¦æ¡H
           iLo_St_Ret_Perf(j, i) = Application.WorksheetFunction.VLookup(iLo_St_Ret(j, i), data, 2, False)

¨C¤ë³ø¹S²v r2.zip (51.06 KB)

¤U¸ü¦¸¼Æ: 14

2013-11-28 16:39

ÁÂÁ¡C

stevenliu555

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

stevenliu555

¦^´_ 2# GBKEE
·PÁ«ü¾É¡A±zªº«ü¾É§Ú¤F¸Ñ¤F¡A¦ý¬O§ÚªººÃ°Ý¬O¬°¤°»ò¤U­±³o­ÓVBA½XªºA¬O¿ù»~
A = Application.VLookup(iLo_St_Ret(j, i), data, 2, False)
·íj=2, i=1®É¡A(iLo_St_Ret(j, i)= -5.2863¡A¥ÎVLOOKUP¨ç¼Æ®É¡AÀ³¸Ó±o¨ì¹j¾ÀÀx¦s®æªº3.2559
§â¥¦©ñ¦b¤u§@ªíªºÀx¦s®æ¸Ì¡A¥¦ªº­È¤]¬O¹j¾ÀÀx¦s®æªº3.2559

¦¹Àɮ׸̡A§Ú­n¼gªºVBA¬O¿ï¨úBÄæ²Ä1¤j­È¡A±o¨ì¨ä¥kÃäÀx¦s®æªº­È¡AµM«á¿ï¨úBÄæ²Ä1¤p­È¡A±o¨ì¨ä¥kÃäÀx¦s®æªº­È¡AµM«á°µ¨ä¥L­pºâ¡A
µM¦Ó²Ä1¤j­È±o¥kÃäÀx¦s®æªº­È¦³±o¨ì¡A²Ä1¤p­È±o¥kÃäÀx¦s®æªº­È¨S¦³±o¨ì¡A¤£ª¾¹D¬°¤°»ò?

³Â·Ð«ü¾É

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

c_c_lai

¦^´_ 4# GBKEE
°}¦C½d³ò¶W¥X¯Á¤Þ½d³ò¡C

1.     '  ReDim iWi_St_Ret(2 To iRowNo, 1 To 5)
   
2.     '  ReDim iLo_St_Ret(2 To iRowNo, 1 To 5)
   
3.     '  ReDim iWi_St_Ret_Perf(2 To iRowNo, 1 To 5)
   
4.     '  ReDim iLo_St_Ret_Perf(2 To iRowNo, 1 To 5)
   
5.     ReDim iWi_St_Ret(2 To iColumnNo, 1 To 5)
   
6.     ReDim iLo_St_Ret(2 To iColumnNo, 1 To 5)
   
7.     ReDim iWi_St_Ret_Perf(2 To iColumnNo, 1 To 5)
   
8.     ReDim iLo_St_Ret_Perf(2 To iColumnNo, 1 To 5)

½Æ»s¥N½X

GBKEE

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

stevenliu555

¦^´_ 6# GBKEE
±z¦n¡G

¤Ó¦n¤F¡A¯uªº¥i¥H­C¡C·PÁ¡C

¥t¥~¡A¦A½Ð±Ð¤@ÂI¦p¤U:
°}¦C¼Æ­È¦p¦ó¶K¤WÀx¦s®æ¡A¨Ò¦p¡A°}¦Ckkk(1 to 5, 1 to 10)ªº¼Æ­È, ¦p¦óÀx¦s¨ìrange("A1:J5")¤ºªºÀx¦s®æ¡H

ÁÂÁÂ

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

stevenliu555

±z¦n¡G

¤Ó¦n¤F¡A·P¿E«ü¾É¡C³£OK¤F¡C