ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

½Ð°Ý¡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)

ÁÂÁ¡C

stevenliu555
Engr<i>[12]<strong>

¦^´_ 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. iRowNo = Sheets("­ÓªÑ³ø¹S²v").Range("A65536").End(xlUp).Row
  9. iColumnNo = Sheets("­ÓªÑ³ø¹S²v").Range("HV1").End(xlToLeft).Column

  10. For j = 2 To iColumnNo
  11.     For i = 1 To 5
  12.         iWi_St_Ret_Perf(j, i) = 0
  13.         iLo_St_Ret_Perf(j, i) = 0
  14.         iWi_St_Ret(j, i) = Application.WorksheetFunction.Large(Range(Cells(2, j), Cells(iRowNo, j)), i)
  15.         iLo_St_Ret(j, i) = Application.WorksheetFunction.Small(Range(Cells(2, j), Cells(iRowNo, j)), i)
  16.         Set data = Range(Cells(j, 2), Cells(100, j + 1))
  17.         A = Application.VLookup(iWi_St_Ret(j, i), data, 2, False)
  18.         'A =>¦³§ä¨ì¶Ç¦^¼Æ­È,¤Ï¤§ ¶Ç¦^¿ù»~­È
  19.         iWi_St_Ret_Perf(j, i) = IIf(Not IsError(A), A, 0)
  20.         A = Application.VLookup(iLo_St_Ret(j, i), data, 2, False)
  21.         iLo_St_Ret_Perf(j, i) = IIf(Not IsError(A), A, 0)
  22.     Next i
  23. Next j
  24. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

RE: ¬°¤°»òVLOOKUP¤£¯à°õ¦æ¡H

¦^´_ 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¬°¤°»ò?

³Â·Ð«ü¾É
Engr<i>[12]<strong>

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-11-29 09:57 ½s¿è

¦^´_ 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. Dim iWi_St_Ret() As Double  'Âùºë«×¯BÂI¼Æ
  5. Dim iLo_St_Ret() As Double  '() °ÊºA°}¦C
  6. Dim iWi_St_Ret_Perf() As Double
  7. Dim iLo_St_Ret_Perf() As Double
  8. Dim i, j, k, n As Integer
  9. Dim c, r As Integer
  10. Dim data As Range
  11. iRowNo = Sheets("­ÓªÑ³ø¹S²v").Range("A65536").End(xlUp).Row
  12. iColumnNo = Sheets("­ÓªÑ³ø¹S²v").Range("HV1").End(xlToLeft).Column
  13. 'ReDim ³¯­z¦¡  ¦bµ{§Ç¼h¦¸¤¤¥Î¨Ó­«·s°t¸m°ÊºA°}¦CÅܼƪºÀx¦sªÅ¶¡¡C
  14. ReDim iWi_St_Ret(2 To iColumnNo, 1 To 5)
  15. ReDim iLo_St_Ret(2 To iColumnNo, 1 To 5)
  16. ReDim iWi_St_Ret_Perf(2 To iColumnNo, 1 To 5)
  17. ReDim iLo_St_Ret_Perf(2 To iColumnNo, 1 To 5)

  18. For j = 2 To iColumnNo
  19.     For i = 1 To 5
  20.         'iWi_St_Ret_Perf(j, i) = 0     '¤£¥²¬°0
  21.         'iLo_St_Ret_Perf(j, i) = 0
  22.        Debug.Print iWi_St_Ret(j, i)
  23.         iWi_St_Ret(j, i) = Application.WorksheetFunction.Large(Range(Cells(2, j), Cells(iRowNo, j)), i)
  24.         iLo_St_Ret(j, i) = Application.WorksheetFunction.Small(Range(Cells(2, j), Cells(iRowNo, j)), i)
  25.         'Set data = Range(Cells(j, 2), Cells(100, j + 1))  '¥t¤@¿ù»~ÂI Cells(j, 2)=>¤@ª½¬OBÄæ
  26.         'Set data = Range(Cells(2, j), Cells(100, j + 1))  'Äæ¦ìÀHµÛj
  27.         Set data = Range(Cells(2, j), Cells(iRowNo, j + 1)) '«Øij¦C¦ì¥Î iRowNo
  28.         'Debug.Print data.Address             '¥i¬Ý¬Ý½d³ò
  29.         iWi_St_Ret_Perf(j, i) = Application.VLookup(iWi_St_Ret(j, i), data, 2, False)
  30.         iLo_St_Ret_Perf(j, i) = Application.VLookup(iLo_St_Ret(j, i), data, 2, False)
  31.     Next i
  32. Next j
  33. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 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

TOP

¦^´_ 5# c_c_lai
4#¤w§ó¥¿,¤S±¼ªÛ³Â¤F,·PÁ§iª¾,
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 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

ÁÂÁÂ
Engr<i>[12]<strong>

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

±z¦n¡G

¤Ó¦n¤F¡A·P¿E«ü¾É¡C³£OK¤F¡C
Engr<i>[12]<strong>

TOP

        ÀR«ä¦Û¦b : ¦h°µ¦h±o¡C¤Ö°µ¦h¥¢¡C
ªð¦^¦Cªí ¤W¤@¥DÃD