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

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

·PÁª©¤jªº¤À¨É¡A¥Ø«eÁÙ¬OµLªk¤U¸ü°Ñ¦Ò.. ¥u¯à³z¹L¤À¨Éªºµ{¦¡½X¨Ó´úÅç

TOP

¦^´_ 10# infoverdad


½Ð°Ý infoverdad

±zªþÀɪºtest¡A­Y­n§â¸ê®ÆÅã²{¤è¦¡¡A±q¡y¥ª¥k¡z§ï¬°¡y¤W¤U¡z®É­n¦p¦ó§ï¡A¬ã¨s¤F¦n¤[¡A·Q±q offset¥h§ï¡A¥i¬O§ä¤£¦a¤è¥i¥H§ï?¥i¥H«ü¾É¤@¤U¶Ü?
peter460191

TOP

·PÁ¤À¨É, ÁÂÁÂ

TOP

·PÁ¤À¨É, ÁÂÁÂ

TOP

§Ú¦n·Q¤U¸ü¨Ó¬Ý¬Ý¡A¥ú¬O¬Ý¥N½X¹ê¦b¬Ý¤£©ú¥Õ

TOP

¦^´_  infoverdad
¤£¿ùªº¤À¨É,¥t¥Î Find ¤èªk  ¤À¨É.
GBKEE µoªí©ó 2011-7-18 20:23


GBKEEª©¤j¦n
¤p§Ì¬O­è¶}©l¦Û¾Çvbaªºªì¤ßªÌ¡A¬Ý¨ì³o¼ÒÀÀvlookupªºµ{¦¡«Ü·Q¾Ç²ß¡C
¥Ñ©óµù¥U½×¾Â¤]¨S¦h¤[¡A©Ò¥H¨S¿ìªk¤U¸üinfoverdad¤j¤jªºªþ¥ó¡C
¥u¦n¬Ý±z¼gªºµ{¦¡¨Ó´¢¼¯¡A¥u¬Oê©ó§Ú¬Oªì¤ßªÌ¡A¨ä¹ê¤]®Ú¥»¬Ý¤£À´§A¦b¼g¤°»ò¡C
«á¨Ó§Ú¥u¦n¤@¥y¤@¥yªº¥ÎF1ºCºC¬d¡AµM«á¦A¬Ýinfoverdad¤j¤j¶Kªº¹Ï¡A¥Î½M²qªº¤è¦¡°µ¥X¨º¤@­Ó¤u§@ªí¡C
§Ú¤£½T©w§Ú°µªº¹ï¤£¹ï¡A¦Ü¤Ö¯àµo´§¸òinfoverdad¤j¤j»¡ªºÃþ¦üvlookup¬d¸ß¥\¯àªº®ÄªG¡C

¥H¤U¡A·Q½Ð±zÀ°§Ú¬Ý¬Ý§Ú°µªº¹ï¤£¹ï¡A§Ú¬Oª½±µ¶K¤W±zªºµ{¦¡¡AµM«áµe¥X¨Óªº¤u§@ªí¡C
vlookup.JPG
2014-5-2 02:26


¤j·§´N¬O¹Ï¤¤ªº¤u§@ªí¶K¤W±zªºµ{¦¡¥i¥H¥¿±`¹B§@¡C

¦ý­«ÂI¬O§Ú¤j·§ª¾¹Dµ{¦¡¤¤¨C¤@¦Cªº§@¥Î¡A¦ý§Ú¤£À´¨C¤@¥y¹ê»Ú¤Wªº¥Îªk¡C
©Ò¥H·Q½Ð±Ð±z¡A¤£¾å±o±z¯à¤£¯à¥Î¥Õ¸Ü¤@ÂIªº¤è¦¡¬°§Ú¸ÑÄÀ¤@¤U¡AÁÂÁÂ~~

Private Sub Worksheet_Change(ByVal Target As Range)  '³o¤@¥y¬O¤£¬O·ítargetªºÀx¦s®æ¦³Åܰʪº®É­Ô´NÅýsub¤ºªºµ{¦¡°õ¦æ? ¥u¬O¬°¤°»ò³o­Ósub¥²¶·¥[¤Wprivate?

    Dim wsLists As Worksheet, Rng As Range 'dim§Ú½®Ñª¾¹D¤j·§¬O«Å§iªº·N«ä§a¡A©Ò¥H¾ã¥yªº·N«ä¬O«Å§iwsLists¬O¤u§@ªí¡Arng¬O½d³ò?

    If Target.Count > 1 Then Exit Sub '³o¥y´N¬O§Ú¹J¨ì²Ä¤@­Ó¤£·|ªº¤F¡Aifªº³¡¤À¨S¤°»ò°ÝÃD¡Atarget¸òExit Sub§Ú¤]¾å±o¡A¥u¬OCount¬O¤°»ò·N«ä°Ú?§Ú·d¤£²M·¡Count¡A¥ÎF1ªº»¡©ú§Ú¹ê¦b¤]¬Ý¤£À´@@

    Set wsLists = Worksheets("Lists") '³o­Ó¬O§âLists³o­Ósheet«ü©wµ¹wsLists

    Application.EnableEvents = False '³o¥y§Úgoogle¤F¦n¤[¡A¥bª¾¥b¸Ñ¡A¬O¤£¬O«üfalseªº®É­Ô¥ý°±¤î³o¬qµ{¦¡ªº¹B§@¡Aµ¥³QApplication.EnableEvents = False¸òtrue¥]°_¨Óªº³o¬qµ{¦¡½T¹ê¶]§¹¡AApplication.EnableEvents Åܦ¨true¡Aµ{¦¡¤~·|½T¹ê°õ¦æ?

    If Target.Column = 2 Then '³o¬O«ütargetªº¦æ¼Æ=2´Nthen

        Set Rng = wsLists.Range("A:A").Find(Target, LookAt:=xlWhole, MatchCase:=True) '³o­Ó¬O¥Îfind¶}©l¬d¸ß¤F¡A«ü©wµ¹rng¡A¥u¬O§Ú¤£À´lookat:=xlWhole¬O¤°»ò·N«ä¡AF1ªº»¡©ú¤]¨S¦³¯S§O»¡©úxlWhole¬O¦b·F¹À¡AMatchCase:=True¬O§â¤j¤p¼g¤]¦C¤J§PÂ_±ø¥ó¸Ì§a?
      
          With Target.Offset(0, 1) '³oÃä¬OÅýtargetªºµ²ªG©¹¥kÄƤ@®æ¡A¥u¬O§Ú¤£À´¬°¤°»ò­n¥Îwith

            If Not Rng Is Nothing Then .Value = Rng.Offset(0, 1).Value Else .Value = ""  'Not Rng Is Nothing¡A§Ú·Q¤F«Ü¤[¡A©Ò¥HÀ³¸Ó¬O­t­t±o¥¿ªº·N«ä?

¤U­±ªºªF¦è´N¬O¦A­«½Æ¤@¦¸¥u¬O¤è¦V¤£¦P¹ï§a?

        End With
    ElseIf Target.Column = 3 Then
        Set Rng = wsLists.Range("B:B").Find(Target, LookAt:=xlWhole, MatchCase:=True)
        With Target.Offset(0, -1)
            If Not Rng Is Nothing Then .Value = Rng.Offset(0, -1).Value Else .Value = ""
        End With
    End If
    Application.EnableEvents = True
End Sub

¥H¤W¡A§Ú°Ýªº¤è¦¡«ÜµL¸£¡A¦]¬°§Ú¹ïVBA¹ê¦b¬O¨S¤°»ò°ò¦¥i¨¥¡C
¤£¹L§Ú¬O¯uªº«Ü·Q¾Ç¡A¦pªGGBKEEª©¤j¤£¶û³Â·Ðªº¸Ü¡AÁٽбzÀ°§Ú¸Ñ´b¤@¤U¡C
½ÐºÉ¥i¯à¥Õ¸Ü¤@ÂI¡AF1ªº»¡©ú¦³ªº³¡¥÷§Ú¬Ý±o«Ü¼Ò½k.......
ÁÂÁÂ~~(¸÷)

TOP

¦^´_ 2# GBKEE
¨C¦¸¬ã¨s¤j¤jªºVBA§¡¦³«Ü¤jªº¦¬Àò, ÁÂÁÂ!!
¦ý³o¥yªº§@¥Î¬O¤°»ò, ÁÙ¬O¤£¤F¸Ñ, ¯à¤£¯à¶i¤@¨B»¡©ú, ÁÂÁÂ!!
  1. If Target.Count > 1 Then Exit Sub
½Æ»s¥N½X

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

¦^´_ 18# GBKEE
ÁÂÁª©¤jªº«ü¾É!!
¤@ª½¥H¬° Worksheet_Change ¬O«ü¨Ï¥ÎªÌ¾Þ§@¤Þµoªº,
¨S·Q¨ìVBA¤]¥i¤ÞµoWorksheet_Change,
¥B¥i¤@¦¸¥i¤Þµo¤@­Ó¤j½d³ò, ÁÂÁª©¤jªº«ü¾É!!

TOP

¦^´_ 18# GBKEE
§Ú¤S¿ù¤F,
¨Ï¥ÎªÌ¾Þ§@¤]¥i¤@¦¸¤Þµo¤@¤j½d³ò,
ÁÂÁÂ!!

TOP

        ÀR«ä¦Û¦b : ¬O«D·í±Ð¨|¡AÆg¬ü§@ĵ±§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD