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

[µo°Ý] VLOOKUP°ÝÃD½Ð±Ð

[µo°Ý] VLOOKUP°ÝÃD½Ð±Ð

DEAR ALL¤j¤j
  ½Ð°Ý¦p¤U°õ¦æ«á¥X²{¿ù»~.§ì¤£¨ì¸ê®Æ??
½Ð°Ý¦ó³B¦³°ÝÃD  ·Ð¤£§[½ç±Ð.  THANKS*10000
*SHEET1 ¤§ AÄæ¹ïÀ³ SHEET2¤§AÄæ«á§ì¨úSHEET2¤§BÄæ¦ÜSHEET1 ¤§ BÄæ*

Sub AA()
Sheet1.Select
Range("A1").Select
For X = 2 To 4
If 1 = 1 Then
M = Sheet1.Cells(X, 1)
   mymax = Evaluate("VLOOKUP(M,¤u§@ªí2!A:B,2,FALSE)")
   Sheet1.Cells(X, 2) = mymax
End If
Next

End Sub
ù

¦^´_ 1# rouber590324

§ï¦¨  Evaluate("VLOOKUP(""" & M &  """,¤u§@ªí2!A:B,2,FALSE)")

TOP

DEAR  diolin  ¤j¤j
  TEST «á§¹¥þ²Å¦X»Ý¨D.·PÁ±z¤§«ü¾É THANKS*10000
ù

TOP

¦Û­qªí³æ¤¤ªºVLOOKUP¨ç¼ÆÀ³¥Î°ÝÃD

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2015/3/25 07:32): ªþÀÉ¥¢±Ñ,½Ð­«¶Ç

½Ð±Ð¦U¦ì¹F¤H:
¤p§Ì·Q¦b¦Û­qªí³æ¤¤À³¥ÎVLOOKUP¨ç¼Æ
¦p¤U¹Ï©Ò¥Ü
½Ð±Ð¦U¦ì¥ý¶iµ{¦¡½XÀ³¸Ó¦p¦ó¼g!!?
ÁÂÁÂ!!
[attach]20496[/attach]

TOP

VLOOKUP¨ç¼ÆÂàVBA°ÝÃD½Ð±Ð

DEAR ALL¤j¤j
¹Ï¤G¬°µ{¦¡½X
¹Ï¤@¤§ Z ­n¦p¦ó§ï¤è¥i°õ¦æ.·Ð¤£§[½ç±Ð.  THANKS*10000

¹Ï¤@
mymax = Evaluate("VLOOKUP(""" & M & """,'µ²ªG2'!A:Y,Z,FALSE)")
¹Ï¤G
Sub ¤@()
Sheet6.Select
Range("A1").Select
Sheet6.[D4:AA7].ClearContents
  For X = 4 To 7
  For Y = 4 To 7
  For Z = 2 To 5
   If 1 = 1 Then
     M = Sheet6.Cells(X, 3)
     mymax = Evaluate("VLOOKUP(""" & M & """,'µ²ªG2'!A:Y,Z,FALSE)")
     If Application.IsError(mymax) = True Then mymax = "" '¨ú¥NVLLOUP§PÂ_2
     Sheet6.Cells(X, Y) = mymax
   End If
Next
Next
Next
End Sub
ù

TOP

¦^´_ 1# rouber590324
  1. mymax = Application.WorksheetFunction.VLookup(M, Sheets("µ²ªG2").[A:Y], Z, False)
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

DEAR ¤j¤j
´ú¸Õµ²ªG  4-7Äæ ³£¶]¥X  Z=5¤§­È
µL¨Ì Z=2¤§­È¦Ü 4Äæ
µL¨Ì Z=3¤§­È¦Ü 5Äæ
µL¨Ì Z=4¤§­È¦Ü 6Äæ
µL¨Ì Z=5¤§­È¦Ü 7Äæ
--·Ð¤£§[½ç±Ð  THANKS*10000
For Z = 2 To 5
For Y = 4 To 7

mymax = Application.WorksheetFunction.VLookup(M, Sheets("µ²ªG2").[A:Y], Z, False)
ù

TOP

¦^´_ 7# rouber590324
µ²ªG2'!A:Y ³o½d³ò¬O³o¼Ë¶Ü?
  1. mymax = Application.WorksheetFunction.VLookup(M, Sheets("µ²ªG2").[A:A].Resize(, Y), Z, False)
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

DEAR ¤j¤j
1. mymax = Application.WorksheetFunction.VLookup(M, Sheets("µ²ªG2").[A:A].Resize(, Y), Z, False)- ¨q¥X    µLªk¨ú±oÃþ§O    WorksheetFunctionªºVLookupÄÝ©Ê.
  1.1 ·Ð¤£§[½ç±Ð.
2.¤p§Ì¥ý¥Î¦p¤U¸ûÂÐÂø¤§¤è¦¡¹B§@.

Sub ¤@¯Å()
Sheet6.Visible = True
Sheet1.Visible = False
Sheet6.Select
Range("A1").Select
Sheet6.[D4:AC7].ClearContents
  For X = 4 To 7
   If 1 = 1 Then
     M = Sheet6.Cells(X, 3)
     mymax = Evaluate("VLOOKUP(""" & M & """,'µ²ªG2'!A:AA,2,FALSE)")
     If Application.IsError(mymax) = True Then mymax = "" '¨ú¥NVLLOUP§PÂ_2
     Sheet6.Cells(X, 4) = mymax
     
     mymax1 = Evaluate("VLOOKUP(""" & M & """,'µ²ªG2'!A:AA,3,FALSE)")
     If Application.IsError(mymax) = True Then mymax = "" '¨ú¥NVLLOUP§PÂ_2
     Sheet6.Cells(X, 5) = mymax1
     
     mymax2 = Evaluate("VLOOKUP(""" & M & """,'µ²ªG2'!A:AA,4,FALSE)")
     If Application.IsError(mymax) = True Then mymax = "" '¨ú¥NVLLOUP§PÂ_2
     Sheet6.Cells(X, 6) = mymax2
     
     mymax3 = Evaluate("VLOOKUP(""" & M & """,'µ²ªG2'!A:AA,5,FALSE)")
     If Application.IsError(mymax) = True Then mymax = "" '¨ú¥NVLLOUP§PÂ_2
     Sheet6.Cells(X, 7) = mymax3

     
    Range("A1").Select
   End If
Next

End Sub
ù

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2015-3-26 11:47 ½s¿è

¦^´_ 9# rouber590324
¨q¥X    µLªk¨ú±oÃþ§O    WorksheetFunctionªºVLookupÄÝ©Ê.

³o²{¶H°Q½×°Ï¦³°Q½×¹L: (http://forum.twbts.com/viewthread.php?tid=10339)
·íApplication.WorksheetFunction.(¤u§@ªí¨ç¼Æ),¶Ç¦^¿ù»~­È®É,VBA·|¦³¿ù»~«H®§.
®³±¼WorksheetFunction¥i­×¥¿.
  1. mymax = Application.VLookup(M, Sheets("µ²ªG2").[A:A].Resize(, Y), Z, False)
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : §g¤l¥ß«í§Ó¡A¤p¤H«í¥ß§Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD