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

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

¦^´_ 18# GBKEE


    ·PÁÂGBKEEª©¤jªº¤À¨É
©Ò¥HCount´N¬O­pºâÀx¦s®æªº¼Æ¶q
¥i¬O³o¼Ë¤p§Ì¤S¦h¤F´X­ÓºÃ°Ý¡A¤£¾å±oGBKEEª©¤j¬O§_Ä@·N¸Ñ´b

1. ¦b±zfind¤èªk¤º¬O¼gif Target.Count > 1 Then Exit sub
    ½Ð°Ý¬O¦b¤°»ò±¡ªp¤§¤UCount¤~·|¤p©ó1©O? ³o¼Ë¼g¬O¬°¤FÁקK¤°»ò±¡ªpµo¥Í©O?

2. ¦b°õ¦æ¹L±z¤W­±¤À¨ÉªºEx¤§«á¡A§Ú¸ÕµÛ¶Ã§ï¸Ì­±ªºµ{¦¡¬Ý¦³¤°»ò®ÄªG¡C
    µM«á§Ú§ï¦¨
   Private Sub Worksheet_Change(ByVal Target As Range)

      if  Target.Count < 1 Then

          MsgBox Target.Count
   
      End If

   End Sub
§Ú¥D­n¬O·Q¸Õ¸Õ¬Ý­×§ï¦¨±z¦bfind¤èªk¸Ì­±ªº¥Îªk·|¦³¤°»ò®ÄªG
µ²ªG§Úµo²{<1ªº®É­Ô§Y«K§Ú¿ï¾Ü¨â­Ó¥H¤WªºÀx¦s®æ¤]¤£·|°õ¦æMsgBox
°£«D¬O¼g¦¨=1¡A³o¼Ë¿ï¾Ü¤@­ÓÀx¦s®æªº®É­Ô¥i¥H¥¿±`ªº°õ¦æMsgBox
¦pªG¼g¦¨<=1©Î>=1ªº®É­Ô¡A¤]¥u¦³¦b¿ï¾Ü¤@­ÓÀx¦s®æªº®É­Ô¤~¯à¥¿±`°õ¦æMsgBox
½Ð°Ý³o¬O¬°¤°»ò©O?
¼g¦¨<1ªº®É­Ô¤£¬O¸Ó¥Nªí§Ú¿ï¾Ü¤@­Ó¥H¤WªºÀx¦s®æ®É¡A´N¯àÅã¥Ü¥¿±`ªºµ²ªG¶Ü?

3. ±z¦bEx³o¬qµ{¦¡¤¤¥Î¨ì¤FOption Explicit
    §Ú¥ÎF1»¡©ú¬d¨ìªº¬O»¡Option Explicit¬O¥Î¨Ó±j¨î¨C­ÓÅܼƳ£¤@©w­n«Å§i
    ¬°¤°»ò±z¦bEx¤¤­n¯S§O¥[¤J³o¤@¬q©O?
    §Ú¸ÕµÛ§âOption Explicit§R°£¡A¤]¬O¯à°÷¥¿±`¹B§@¡A¨º¥[¤J³o¤@¬qªº¥Î·N¬O¤°»ò©O?

¤p§Ì¾|¶w¡A§Æ±æGBKEEª©¤j¯à¤£§[½ç±Ð~~~~~ÁÂÁÂ~~

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

¦^´_ 22# GBKEE


    ·PÁÂGKBEEª©¤jªº¸Ñµª~~
©¿µMı±o¦Û¤v¦nÄø¡A­ì¨Ó§Ú°Ý°ÝÃDªº¤è¦V¿ù¤F¡A¦]¬°§Ú§¹¥þ¬Ý¿ù¤F¡C
¤j·§¬O¬Ý¤Ó¤[¬Ý¨ì²´³£ªá¤F¡A§Ú¤@ª½¥H¬°±z¬O¼g If Target.Count < 1  @@
©Ò¥H§Ú¤~·|¤@ª½¥H¬°¬O¬°¤FÁקK¥X²{Count¤p©ó1ªºª¬ªpµo¥Í¡A­ì¨Ó®Ú¥»¤£¥i¯à·|¦³Count<1ªº±¡ªp....
¯uªº¬O«Ü©êºp~~:P

§Ú¤§«e¦³¸Õ¹L¦bfind¤èªk¤¤§â If Target.Count > 1 Then Exit Sub®³±¼´ú¸Õ¡A¥i¬O°»¿ùªº¥\¯à¨S¦³ªý¤î§Ú¡A¾ã­Óµ{¦¡ÁÙ¬O°õ¦æªº«Ü¶}¤ß¡A¥\¯à¤]³£¥¿±`¡C
©Ò¥H§Ú¤~·d¤£À´¨º¤@¬q¨ì©³¬O¤°»ò·N«ä~~~
¤Ï­Ë¬O§â Application.EnableEvents = False¸ò Application.EnableEvents = true®³±¼¡A¾ã­Ó´N¶}©l¼É¨«
§Ú·í¤U¤â¨¬µL±¹¡AÁÙ¦n«ö¤UEsc´N°±¤î¤F

³o¼Ë§Ú´N©ú¥Õ¤F¡ACount > 1¬O«ü¦Ü¤Ö¿ï¾Ü¤@­Ó¥H¤WªºÀx¦s®æ
¦pªG¬O³o¼Ëªº¸Ü¡A±zfind¤èªk¤º¼gªº¬O If Target.Count > 1 Then Exit Sub
¥i¬OExit Sub¤£¬O«üÂ÷¶}³o­Ó¨Æ¥ó¶Ü?  ³o¼Ë¾ã¥y¸ÑÄÀ¤U¨Ó¤£´N¬Oµ¥©ó¿ï¾Ü¦Ü¤Ö¤@­Ó¥H¤WªºÀx¦s®æ´NÂ÷¶}³o­Ó¨Æ¥ó?
ÁÙ¬O§Ú²z¸Ñ¿ù»~©O?
·Q¦A¨D¸Ñ¤@¤U~~
¥H¤W¡A¦A¦¸·PÁÂGBKEEª©¤jªÖ¶O¤ß¬°§Ú¸Ñ´b~~

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

½Ð°Ý¦U¦ì°ª¤â¡A¦btarget ªº¥Îªk  ¡A¬O¦p¦ó¨Ï¥Î

TOP

        ÀR«ä¦Û¦b : ¦a¤WºØ¤Fµæ¡A´N¤£©öªø¯ó¡F¤ß¤¤¦³µ½¡A´N¤£©ö¥Í´c¡C
ªð¦^¦Cªí ¤W¤@¥DÃD