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

[µo°Ý] ½Ð°ÝÃö©ó¸ó¤u§@ªívlookupªºvba»yªk

[µo°Ý] ½Ð°ÝÃö©ó¸ó¤u§@ªívlookupªºvba»yªk

  1. Private Sub WORKSHEET_CHANGE()
  2. Dim data As Range
  3. Set data = ['sheet2'!$A$1:$B$10]
  4. [b1] = Application.WorksheetFunction.VLookup([a1], data, 2, 0)
  5. End Sub
½Æ»s¥N½X
¥H¤W¬O§Úª¦½×¾Âªº¤å©Ò¼g¥X¨Óªº ·Q­n¦bsheet1ªºa1:a10¥´¤W½s¸¹ «hb1:b10°Ñ³ya1:a10ªº½s½X§ä´Msheet2ªº¸ê®Æ
¦ý «o¥X²{¤F"µLªk¨ú±oÃþ§O worksheetfanctionªºvlookupÄÝ©Ê"³o°T®§

·Ð½Ð¦U¦ì¤j¤j«ü±Ð¤@¤U ¸Ó«ç»ò­×¥¿§¹µ½ «D±`·P®¦!

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-9-16 15:34 ½s¿è

¦^´_ 1# e19821223
  °Ñ¦Ò³o¸Ì stillfish00 ªº¬ã¨s  
  1. Private Sub WORKSHEET_CHANGE()
½Æ»s¥N½X
³oµ{§Ç¦WºÙ,¬OVBA¤u§@ªíª«¥ó¼Ò²Õªº¤º«Ø¨Æ¥ó¦WºÙ,¤£­n¶Ã¥Î
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

»Ý­nªá®É¶¡¬ã¨s ÁÙ¬O¤£¬O«ÜÀ´ ·PÁÂG¤jªº«ü±Ð~

TOP

²Ä¤@¦æ«O«ù¨Æ¥óªº§¹¾ã°Ñ¼Æ
Private Sub WORKSHEET_CHANGE(ByVal Target As Range)
´N¥i¥H°õ¦æ¤F

TOP

·PÁÂO¤jªº¦^ÂÐ §Ú¨Ì·ÓO¤j©Ò¥´ªº §ï¦¨
Private Sub WORKSHEET_CHANGE(ByVal Target As Range)
Dim data As Range
Set data = ['sheet2'!$A$1B$10]
[b1] = Application.WorksheetFunction.VLookup([a1], data, 2, 0)
End Sub

¥i¦æ¤F
¥i¹B§@¤F
¦ý¥X²{¤Fĵ§i¼Ð»y"°ïÅ|ªÅ¶¡¤£¨¬" ¤£ª¾¹D¬O¤°»ò°ÝÃD©O
ÁÙ¦³ §Ú¤µ¤Ñ ¬O¥ÎA1 B1
¦ý §Ú­Y¬O·Q­n¦bA1:A10 ,B1:B10³£·Q­n§e²{ ¨º ­n¥´10¦¸¶Ü?
§Ú¬O«D±`·sªº·s¤â..©|¦b¾Ç²ß ¥ý·PÁ¦U¦ì¸ô¹Lªº°ª¤âªº«ü±Ð

TOP

  1. Private Sub WORKSHEET_CHANGE(ByVal Target As Range)
  2. Dim data As Range
  3. Set data = ['sheet2'!$A$1B$1000]
  4. [b1] = Application.WorksheetFunction.VLookup([a1], data, 2, 0)
  5. End Sub
½Æ»s¥N½X
¥H¤Wªº¤£¯à¨Ï¥Î...·í¿é¤J«á¹q¸£Åܪº¶W¯ÅºC excel¾ã­Ó·í±¼
¤£ª¾¬O§_¦³§ó¦nªº¤è¦¡©O ÁÂÁ«ü±Ð

TOP

¦^´_ 6# e19821223
VBA »¡©ú
  1. EnableEvents ÄÝ©Ê
  2. ½Ð°Ñ¾\®M¥Î¦Ü½d¨Ò¯S©w¦pªG«ü©wª«¥ó¯àIJµo¨Æ¥ó¡A«h¥»Äݩʬ° True¡CŪ/¼g Boolean¡C
½Æ»s¥N½X

WORKSHEET_CHANGE ·í¨Ï¥ÎªÌ©Î¥~³¡³sµ²Åܧó¤u§@ªí¤¤ªºÀx¦s®æ®Éµo¥Í¦¹¨Æ¥ó
[b1] = Application.VLookup([a1], data, 2, 0) ,·|¤@ª½¤Þµo¦¹¨Æ¥ó.
   
  1. Option Explicit
  2. Private Sub WORKSHEET_CHANGE(ByVal Target As Range)
  3.     Dim data As Range
  4.     Application.EnableEvents = False
  5.     Set data = ['sheet2'!$A$1:B$1000]
  6.     [b1] = Application.VLookup([a1], data, 2, 0)
  7.     Application.EnableEvents = True
  8. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

·PÁÂO¤jªº«ü±Ð!
²×©ó¤î¦í¤£¹BÂà¤F!

¤£¹L ·í°õ¦æ¦¹»yªk«á ¦bB1ªº³¡¤À ´N¤£¯à¤â°Ê¿é¤J¤F
¦³¤°»ò¿ìªk¬O¥i¥H»¡¨ÌµM¥i¥H¤â°Ê¿é¤J©O?
¦A¦¸³Â·Ð¤F «D±`·PÁÂ

TOP

¦^´_ 8# e19821223
  1. If Target.Address(0, 0) = "B1" Then Exit Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 9# GBKEE
¦A¦¸ÁÂÁÂG¤j
¤£¹L ·í¦bA¿é¤J­È®É B·|¥X²{"#N/A" ªº±¡ªp(¦]¬°§ä¤£¨ì­È)
¤p§Ì§Ú«Ü³æ¯Âªº¦h¥[¤@­ÓIF »yªk¦p¤U:
  1. If [c13:c32] = "#N/A" Then
  2. [c13:c32] = ""
  3.      
  4. End If
½Æ»s¥N½X
®¦..§Ú¤£¬O«ÜÀ´ªº¬Ý¤F§Ú¦Û¤v¼gªº¤]¯º¤F...¦n¹³¨S¨º»ò³æ¯Â..
®Ú¥»µLªk°õ¦æ...

¦A¦¸½Ð±ÐG¤j¤F

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C
ªð¦^¦Cªí ¤W¤@¥DÃD