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

[µo°Ý] ¤S¾¤VLOOKUP ¶}ÃD

[µo°Ý] ¤S¾¤VLOOKUP ¶}ÃD

¦U¦ì¤j¤j, ¤S«Y¤p§Ì·Q½Ð±Ð¤@¤U, «Y¦Cªí, SHEET1 «Y ¸ê®Æªí,
SHEET2«Y¤u§@ªí, §Ú·Q­nªºµ²ªG: ¥ÎSHEET2 D COLUMN ¬J¸ê®Æ, ¥h¹ï¤ñSHEET1 A COLUMN¬J¸ê®Æ,
²Å¦X±ø¥ó´N¦^¶Ç SHEET1 BCOLUMN ¬J¸ê®Æ, ª½¦ÜSHEET2 D COLUMN ªÅ¥Õ¬°¤î,
¤§«e¸Õ¹L¥ÎVLOOKUP ¬J¤èªk°µ, ¦ý«Y­ø¦¨¥\, §Ú·Q°Ý¤U¦U¦ì¤j¤jÀ³¸ÓÂI°µ¦n??

HELP.zip (50.92 KB)

¦^´_ 1# yowhome
¨Ò¦p¡G
1¡B½Ð¦bSheet2¤§A2Àx¦s®æ¼g¤J
=VLOOKUP(D2,Sheet1!A:B,2,0)
2¡B©¹¤U½Æ»s¤½¦¡§Y¥i
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 2# p212


    ¦hÁ¤j¤j´£¨Ñ, ¦ý³o¤£¬O§Ú·Q­nªº¸ê®Æ, ¦]¬°SHEET2 ªº D COLUMN ¨C¦¸¿é¤Jªº¸ê®Æ³£¤£¦P, §Ú·Q­nªº¬O¦bVBA¤J­±, °µ¨ì§Ú¥H¤Wªº®ÄªG.

TOP

SORRY. ¥i¯à«Y§Ú¥´±o­ø¦n, ¦A¾¤!!

SHEET1 «Y ¸ê®Æªí, SHEET2«Y¤u§@ªí, ¦]¬°SHEET2¬J D COLUMN ¬J¸ê®Æ¨C¦¸³£¤£¦P,
§Ú·Q­nªºµ²ªG: «YVBA¤J­±, ¥ÎSHEET2 D COLUMN ¬J¸ê®Æ, ¥h¹ï¤ñSHEET1 A COLUMN¬J¸ê®Æ,
²Å¦X±ø¥ó´N¦^¶Ç SHEET1 B COLUMN ¬J¸ê®Æ, ª½¦ÜSHEET2 D COLUMN ªÅ¥Õ¬°¤î,
¤§«e¸Õ¹L«YVBA¤J­±¥ÎVLOOKUP ¬J¤èªk°µ, ¦ý«Y­ø¦¨¥\, §Ú·Q°Ý¤U¦U¦ì¤j¤jÀ³¸ÓÂI°µ¦n??

TOP

§Ú¬O¤p¾Ç¥Í¤£¯à¬Ý¨ìªþ¥ó¤º²[¡F«Øij¸Õ¥Î Application.worksheetfunction.Vlookup  .....¦bVBA¤º©I¥s¤u§@ªí¨ç¼Æ

TOP

¦^´_ 4# yowhome
¥ÎSHEET2 D COLUMN ¬J¸ê®Æ, ¥h¹ï¤ñSHEET1 A COLUMN¬J¸ê®Æ

SHEET2 D COLUMN ªº¸ê®Æ¦³³\¦h¬O¤@³s¦ê¬Û¦Pªº­n¦p¦ó¤ñ¹ï,Áٻݦh»¡©ú.(¤W¶Ç½d¨Ò»¡©ú)
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 6# GBKEE


HELP.zip (51.2 KB)

­ø¦n·N«ä, ¦A¾¤, SHEET2 D COLUMN  ¥u«Y¤u§@ªí¨ä¤¤¤@Äæ, §Ú­n±NÊ\¦PSHEET1 ¬J¸ê®Æªí¬Û¹ï, ¦^¶ÇSHEET1 B COLUMN¬J ¸ê®Æ¨ìSHEET2 A COLUMN,
ªþ¥ó¤¤SHEET2 A COLUMN ´N«Y§Ú·Q­n¬Jµ²ªG.

TOP

¦^´_ 7# yowhome

ª½±µ¼g¤J¤½¦¡­pºâ
  1. Sub ex()
  2. With Sheet2
  3.   With .Range(.[A2], .[D2].End(xlDown).Offset(, -3))
  4.   .FormulaR1C1 = "=VLOOKUP(RC[3],Sheet1!R2C1:R65536C3,2,0)"  '¼g¤J¤½¦¡
  5.   .Value = .Value  '¼g¤J­È
  6.    End With
  7. End With
  8. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# yowhome
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Ar(), R As Integer, I As Integer, M As Variant
  4.     With Sheet2
  5.         R = .[d1].End(xlDown).Row      'DÄæ³Ì«á¦³¸ê®Æªº¦C¸¹¼Æ
  6.         .Range("A2:A" & .Rows.Count) = ""      '²M°£AÄ榳¸ê®Æ
  7.         ReDim Ar(2 To R)               '­«¸m°}¦C
  8.         For I = 2 To R
  9.             M = Application.Match(.Cells(I, "D"), Sheet1.Range("A:A"), 0)
  10.             'Match¨ç¼Æ:§ä¤£¨ì¶Ç¦^¿ù»~­È , §ä¨ì¶Ç¦^ ¼Æ¦r
  11.             If IsNumeric(M) Then Ar(I) = Sheet1.Cells(M, "B")
  12.         Next
  13.         .Range("A2:A" & R) = Application.Transpose(Ar)
  14.     End With
  15. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

2­Ó³£¦¨¥\, ¦hÁ¤j¤jÀ°¦£.

TOP

        ÀR«ä¦Û¦b : ¡i»X½ªªº¦Û¥Ñ¡j¤H±`¦b¤°»ò³£¥i¥H¦Û¥Ñ¦Û¦bªº®É­Ô¡A«o³Q³oºØÀH¤ß©Ò±ýªº¦Û¥Ñ»X½ª¡AµêÂY®É¥ú¦Ó²@µLıª¾¡C
ªð¦^¦Cªí ¤W¤@¥DÃD