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

[µo°Ý] ¦p¦ó¦b¦P¤@­ÓÀx¦s®æ¥´¤W¥N½X¶]¥X¹ïÀ³ªº¸ê®Æ

[µo°Ý] ¦p¦ó¦b¦P¤@­ÓÀx¦s®æ¥´¤W¥N½X¶]¥X¹ïÀ³ªº¸ê®Æ

¦U¦ì¤j¤j¡A¤j®a¦n¡G
¦bªþ¥ó¸Ì­±¡A¦³¡u¨®¤l¸ê®Æ¡vªºSheet»P¡u3¤ë¡vªºSheet¡C
1.·Q­n¦b¡u3¤ë¡vªºÀx¦s®æ¥´¤W¨®¤l¥N½X¡A¦b¦P¤@­ÓÀx¦s®æ·|¥X²{¡u¨®¤l¸ê®Æ¡v©Ò³]©w¦nªº" ¨®«¬ "¡C
2.¨C¤@­Ó¤ë³£·|·s¼W¤@±isheet¡A¾Þ§@ªk¦¡¦p¦PNo.1¡C

½Ð°Ñ¦Òªþ¥ó»¡©ú ¨C¤ë³øªí.zip (3 KB)

¦]¬°§Ú¹ïµ{¦¡»yªk¤£¼ô¡A¤£ª¾¹Dµ{¦¡À³¸Ó«ç»ò¼g?·Q±Ï°ª¤â­ÌÀ°¦£¡A«D±`ÁÂÁ¤j®aªºÀ°¦£!

¦^´_ 1# j2888237
ThisWorkbook¼Ò²Õ
  1. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  2. Dim A As Range
  3. Set A = Sheets("¨®¤l¸ê®Æ").[A:A].Find(Target, , , xlWhole)
  4. Application.EnableEvents = False
  5. If Not A Is Nothing Then Target = A.Offset(, 2)
  6. Application.EnableEvents = True
  7. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 1# j2888237

¸Õ¬Ý¬Ý

¨C¤ë³øªí.zip (13.79 KB)
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

¦^´_ 1# j2888237
¸Õ¸Õ¬Ý:
  1. Option Explicit
  2. '¨C·s¼W¤@±i Worksheet, ´N±N¦¹VBA½Æ»s¹L¥h
  3. Private Sub Worksheet_Change(ByVal Target As Range)
  4.     Dim rngD, rngA As Range, sh1 As Worksheet, endRow As Integer
  5.     Dim ¥N½X
  6.     Set sh1 = Sheets("¨®¤l¸ê®Æ")
  7.    
  8.     endRow = sh1.[A2000].End(xlUp).Row
  9.     Set rngA = sh1.[A2].Resize(endRow, 1)
  10.    
  11.     endRow = [D2000].End(xlUp).Row
  12.     Set rngD = [D2].Resize(endRow, 1)
  13.    
  14.     If Not Intersect(Target, rngD) Is Nothing Then
  15.         ¥N½X = Application.Match(Target, rngA, 0)
  16.         On Error Resume Next
  17.         Target = sh1.[A1].Offset(¥N½X, 2)
  18.     End If
  19. End Sub
½Æ»s¥N½X

TOP

¦^´_ 3# li_hsien


    Åv­­¤£°÷¡AµLªk¤U¸üÀÉ®×!!:'(

TOP

¦^´_ 4# yen956


    ½Ð±Ðyen956 ¤j¤j :
    1¤éªºÄæ¦ì¯à¥¿±`¶]¥X¨Ó¡A¦ý¬O2¤é¶}©l´N¤£¦æ¤F¡A
   §Ú¸Õ¹L¦A·s¼WrngE, Âлs
   Set rngE = [E2].Resize(endRow, 1)
    If Not Intersect(Target, rngD) Is Nothing Then
    ¥N½X = Application.Match(Target, rngA, 0)
     On Error Resume Next
    Target = sh1.[A1].Offset(¥N½X, 2)
     End If
    ­×§ï¤@¬q¡A¦ý·|¦³°ÝÃD!!!  :'(

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2014-3-22 04:44 ½s¿è

¦^´_ 6# j2888237
©êºp, ¨Sª`·N¨ì¤é´Á©¹¾îªº,
½Ð±N
Set rngD = [D2].Resize(endRow, 1)
§ï¦¨
Set rngD = [D2].Resize(endRow, 31)
´N¥i¥H¤F, ©êºp!!

¦pªG¤é´Á§ï¦¨±q[E2]¶}©l, «h
Set rngD = [E2].Resize(endRow, 31)
´N¥i¥H¤F

­Y§ï
Set rngE = [E2].Resize(endRow, 31)
«h³o¸Ì¤]­n§ï
If Not Intersect(Target, rngE) Is Nothing Then

TOP

¦^´_ 7# yen956


    «D±`·PÁÂyen956¤j¤j¼ö¤ß¦^ÂÐ :
    ¸g¤j¤jªº«ü¾É«á¡A2¤é¡B3¤é¡K31¤é³£¥i¥H¥¿±`Åã¥Ü¨®¸¹©Ò¹ïÀ³ªº¤j¤p¡A
   ¥i¬O³o¸Ì¦³­Ó¤p°ÝÃD´N¬O:¦pªG¥q¾÷¦³15¦ì¡A¦b³o¥÷excelªí¤¤­Ë¼Æ4¦ì¥q¾÷¦b¸Ó¤ë¥÷ªº1¤é¨ÃµL¶}¨®¡A³o¼Ë·|Åý2¤é¤§«á­Ë¼Æ3¦ì¥q¾÷©Ò¿é¤Jªº¥N½X±a¤£¥X¹ïÀ³ªº¸ê®Æ¨Ó¡A°£«D¦Ü¤Ö¦b1¤é­Ë¼Æ²Ä2¦ì¥q¾÷­n¦³¡u¤j¤p¡v¸ê®Æ¡A§_«h2¤é¶}©l¤§«á³o¥÷excelªí­Ë¼Æ¨º´X¦ì¥q¾÷¡A³£±a¤£¥X¸ê®Æ¡C

¦A½Ð¤j¤j­n¦p¦ó¸Ñ¨M³o­Ó°ÝÃD?
ÁÂÁÂyen956¤j¤j¦Ê¦£¤§¤¤¦^ÂСK ^^

TOP

¦^´_ 5# j2888237

§Ú¬O¤À§O©ñ¨â­Ó¼Ò²Õ¸Ì­±

¤£ª¾³o¼ËO¤£OK
  1. Option Explicit

  2. Sub test()

  3.     Application.OnKey "{ENTER}", "car_info"

  4. End Sub
½Æ»s¥N½X
  1. Option Explicit

  2. Sub car_info()

  3.     If Selection.Value <> "" Then
  4.         
  5.         If IsError(Application.VLookup(Selection.Value, Worksheets("¨®¤l¸ê®Æ").Range("A:C"), 3, 0)) = False Then
  6.             Selection.Value = Application.VLookup(Selection.Value, Worksheets("¨®¤l¸ê®Æ").Range("A:C"), 3, 0)
  7.         Else
  8.             Selection.Value = "µL¦¹¥N½X"
  9.         End If
  10.     End If
  11.    
  12. End Sub
½Æ»s¥N½X
°Ñ¦Ò¬Ý¬Ý
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

¦^´_ 8# j2888237
¤j¤j§A¦n:
½Ð±N
    '¥Ñ[D2000]¶}©l¦V¤W¬d, ª½¨ì§ä¨ì«DªÅ¥Õ®æ¬°¤î
    endRow = [D2000].End(xlUp).Row
§ï¦¨(¦]¥q¾÷¦b ÄæC)
    '¥Ñ[C2000]¶}©l¦V¤W¬d, ª½¨ì§ä¨ì«DªÅ¥Õ®æ¬°¤î
    endRow = [C2000].End(xlUp).Row
¤]¥i§ï¦¨ endRow = 2000, ©T©w rngD ªº½d³ò,
¦ý½d³ò·U¤j. Excel ªº°õ¦æ®Ä²v·U®t.
¤S¤W¦¸¨S»¡²M, rngD ¥u¬O­ÓÅܼÆ(©R¦W¬° Rng, myRng, XY §¡¥i),
»P ÄæD µLÃö, »P
    Set rngD = [D2].Resize(endRow, 31)
¤~¦³Ãö.

TOP

        ÀR«ä¦Û¦b : °ß¨ä´L­«¦Û¤vªº¤H¡A¤~§ó«i©óÁY¤p¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD