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

[¤À¨É] VBA¤À¨É-®ÄªGÆZÃþ¦üVLOOKUP¬d¸ß,¥u¬O¥i¥H¨âÄ椬¬d

¦^´_ 1# infoverdad
¤£¿ùªº¤À¨É,¥t¥Î Find ¤èªk  ¤À¨É.
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim wsLists As Worksheet, Rng As Range
  3.     If Target.Count > 1 Then Exit Sub
  4.     Set wsLists = Worksheets("Lists")
  5.     Application.EnableEvents = False
  6.     If Target.Column = 2 Then
  7.         Set Rng = wsLists.Range("A:A").Find(Target, LookAt:=xlWhole, MatchCase:=True)
  8.         With Target.Offset(0, 1)
  9.             If Not Rng Is Nothing Then .Value = Rng.Offset(0, 1).Value Else .Value = ""
  10.         End With
  11.     ElseIf Target.Column = 3 Then
  12.         Set Rng = wsLists.Range("B:B").Find(Target, LookAt:=xlWhole, MatchCase:=True)
  13.         With Target.Offset(0, -1)
  14.             If Not Rng Is Nothing Then .Value = Rng.Offset(0, -1).Value Else .Value = ""
  15.         End With
  16.     End If
  17.     Application.EnableEvents = True
  18. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-7-19 08:15 ½s¿è

¦^´_ 4# Hsieh¶Wª©
§Aªºµ{¦¡§ó²¼ä  
¦ý Set A = s.Find(Target, lookat:=xlWhole) ¤§«á¨S¦³³]¤U A Is Nothing ªº±ø¥ó,(³oÀɮתºÅçÃÒ²M³æ½d³ò¤¤¨S¦³ªÅ¥Õ­È)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-7-19 08:48 ½s¿è

¦^´_ 6# Hsieh¶Wª©
§Úªº·N«ä¬O¦³ÅçÃÒ²M³æªºÀx¦s®æ¦¡¬O¤¹³\¿é¤JªÅ¥Õ­Èªº,¦p¿é¤J­È¬O ªÅ¥Õ­È,
Set A = s.Find(Target, lookat:=xlWhole) ¶Ç¦^ A Is Nothing
§A4¼Óªºµ{¦¡, ¦b¼Ó¥DªºÀÉ®×·|²£¥Í¿ù»~.

TOP

¦^´_ 17# yen956
°õ¦æEx¬Ý¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     ActiveSheet.[A1:A10] = ""
  4. End Sub
  5. Private Sub Worksheet_Change(ByVal Target As Range)
  6.     'Target ªº«¬ºA¬O Range (¥¦¬O¤@­ÓÀx¦s®æ©ÎÀx¦s®æ½d³ò)
  7.     MsgBox Target.Cells.Count
  8. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 21# dryadf
1. ¦b±zfind¤èªk¤º¬O¼gif Target.Count > 1 Then Exit sub
   A: ¨S¦³Target.Count·|¤p©ó1ªº,  ¬°ÁקK¦p¤UÃþ¦ü¦p¤U±¡ªpµo¥Í.¸ÕµÛ Find¤èªk¤º ¥h±¼³o¬q¬Ý¬Ý if Target.Count > 1 Then Exit sub
  1. Option Explicit
  2. Sub Ex()
  3.     ActiveSheet.[A1:A10] = ""
  4. End Sub
  5. Private Sub Worksheet_Change(ByVal Target As Range)
  6.     Dim E As Range
  7.     For Each E In Target
  8.         MsgBox E.Address
  9.     Next
  10. End Sub
½Æ»s¥N½X
2. ¦b°õ¦æ¹L±z¤W­±¤À¨ÉªºEx¤§«á¡A§Ú¸ÕµÛ¶Ã§ï¸Ì­±ªºµ{¦¡¬Ý¦³¤°»ò®ÄªG¡C
¼g¦¨<1ªº®É­Ô¤£¬O¸Ó¥Nªí§Ú¿ï¾Ü¤@­Ó¥H¤WªºÀx¦s®æ®É¡A´N¯àÅã¥Ü¥¿±`ªºµ²ªG¶Ü?q
A:¬O>=1§a,¥Nªí§Ú¿ï¾Ü¤@­Ó¥H¤WªºÀx¦s®æ.

3. ±z¦bEx³o¬qµ{¦¡¤¤¥Î¨ì¤FOption Explicit
¦pA 1 ¤¤¨SDim E As Range ¨t²Î ·|³qª¾§A­n«Å§iÅܼÆ.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥i¬OExit Sub¤£¬O«üÂ÷¶}³o­Ó¨Æ¥ó¶Ü?  ³o¼Ë¾ã¥y¸ÑÄÀ¤U¨Ó¤£´N¬Oµ¥©ó¿ï¾Ü¦Ü¤Ö¤@­Ó¥H¤WªºÀx¦s®æ´NÂ÷¶}³o­Ó¨Æ¥ó?
dryadf µoªí©ó 2014/5/5 01:08

¨S¿ùªº
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. 'ByVal ¿ï¾Ü©Ê¤Þ¼Æ¡Cªí¥Ü¥H¶Ç­Èªº¤è¦¡¨Ó¶Ç»¼¤Þ¼Æ¡C
  3. 'Target : ÅܼƦWºÙ
  4. 'As Range : «¬ºA¬° Rangeª«¥ó
  5. ' Range ª«¥ó¡A¸Óª«¥ó¥Nªí¤@­ÓÀx¦s®æ©ÎÀx¦s®æ½d³ò
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : °µ¸Ó°µªº¨Æ¬O´¼¼z¡A°µ¤£¸Ó°µªº¨Æ¬O·Mè¡C
ªð¦^¦Cªí ¤W¤@¥DÃD