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

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

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

¥»©«³Ì«á¥Ñ infoverdad ©ó 2011-7-14 19:31 ½s¿è

¤À¨É¤§«e°Ñ¦ÒªºVBAµ{¦¡½X

®ÄªGÆZÃþ¦üVLOOKUP¬d¸ß,¥u¬O¥i¥H¨âÄ椬¬d¡C
BÄæ»PCÄæ¬Ò¬°¸ê®ÆÅçÃÒ"²M³æ"ªº¤è¦¡
ÂI¿ï¥ô¤@Äæ¦ì, «h¨ä¹ïÀ³ªº­È·|¦Û°Ê±a¤J
FILLONE.gif
¥ô¶ñ¤@Äæ±a¤J¥t¤@Äæ.rar (8.18 KB)

¦^´_ 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

¥»©«³Ì«á¥Ñ infoverdad ©ó 2011-7-18 22:25 ½s¿è

¦^´_ 2# GBKEE

ÁÂÁÂGBKEEª©¤j,¯u¬O¤Ó´Î¤F!! ¥¦¤£¦ý§ó²¼ä,¤]¸Ñ¨M¤F§Ú¤§«e¦]À³¥Î»Ý­n,¶·³v¤@­×§ï©w¸q¦WºÙªº³Â·Ð!!
¤£¹L¬°ÁקK­×§ïÄæ¦W¡A¦Ó­P¥t¤@ÄæÄæ¦WÅܪťÕ, ÁÙ¬O­n¨Ï¥ÎexitHandler¨Ó³B²z¥¦.  ©Ò¥H§Ú§âµ{¦¡½Xµy·L­×§ï¤F¤@¤U,³o¼Ë§Ú¥¼¨Ó¦b¤u§@¤W´N¥i¥H¦h¦hµ½¥Î¥¦¤F!!
  1. Option Explicit
  2. Private Sub Worksheet_Change(ByVal Target As Range)
  3.     Dim wsLists As Worksheet, Rng As Range
  4.     On Error GoTo errHandler

  5.     If Target.Count > 1 Then Exit Sub
  6.     Set wsLists = Worksheets("Lists")
  7.     Application.EnableEvents = False
  8.    
  9.     If Target.Column = 2 Then
  10.         Set Rng = wsLists.Range("A:A").Find(Target, LookAt:=xlWhole, MatchCase:=True)
  11.         With Target.Offset(0, 1)
  12.              If Not Rng Is Nothing Then .Value = Rng.Offset(0, 1).Value Else: GoTo exitHandler
  13.         End With
  14.         
  15.     ElseIf Target.Column = 3 Then
  16.         Set Rng = wsLists.Range("B:B").Find(Target, LookAt:=xlWhole, MatchCase:=True)
  17.         With Target.Offset(0, -1)
  18.              If Not Rng Is Nothing Then .Value = Rng.Offset(0, -1).Value Else: GoTo exitHandler
  19.         End With
  20.     End If
  21.     Application.EnableEvents = True
  22.    
  23. exitHandler:
  24.   Application.EnableEvents = True
  25.   Exit Sub

  26. errHandler:
  27.   MsgBox Err.Number & ": " & Err.Description
  28.   GoTo exitHandler
  29.    
  30. End Sub
½Æ»s¥N½X

TOP

  1. Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  2. Dim A As Range, c As Integer, s As Range
  3. Application.EnableEvents = False
  4. With Target.Validation
  5. Set s = Evaluate(.Formula1)
  6. End With
  7. Set A = s.Find(Target, lookat:=xlWhole)
  8. c = IIf(A.Column = A.CurrentRegion.Column, 1, -1)
  9. Target.Offset(, c) = A.Offset(, c)
  10. Application.EnableEvents = True
  11. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

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

¦b²M³æ½d³ò§äÅçÃҿﶵªÖ©w§äªº¨ì
§_«hÅçÃÒ´NµLªk³q¹L
¾Ç®üµL²P_¤£®¢¤U°Ý

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

¦^´_ 7# GBKEE


ÁÙ¬O§A¤ß«ä²Ó¿°¡A³o­Ó§PÂ_§Ú­ì¥ý¬O¦³¼g¶i¥h
¥u¬O«á¨Ó»{¬°²M³æ¤ºªº¿ï¶µ©Ò¥H¬Ù²¤
³o­Ó§PÂ_¡A·Q·QÁÙ¬O¥²­n¡A¦]¬°´Nºâ¬OÅçÃÒ¡AÁÙ¬O®e³\²MªÅ¤º®e
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦n·PÁ¨â¦ì¶W¯Åª©¤jªº«ü¾É,µ¥¤@¤U¨Ó¥Î¥Î¬Ý.

TOP

¥»©«³Ì«á¥Ñ infoverdad ©ó 2011-7-19 11:11 ½s¿è

§Ú¦Û¤v­×§ï¤F¤@¤U,¤£¹L¤£ª¾¥i§_¦Aºë²?
¥tªþ¤W´ú¸ÕÀÉ. VBA TEST2.rar (7.57 KB)
  1. Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  2. Dim A As Range, c As Integer, s As Range

  3. On Error GoTo errHandler

  4. Application.EnableEvents = False
  5. With Target.Validation
  6. Set s = Evaluate(.Formula1)
  7. End With
  8. Set A = s.Find(Target, lookat:=xlWhole)


  9. If Not A Is Nothing Then 'Àˬd¬O§_¦³¤º®e³Q²MªÅ

  10. c = IIf(A.Column = A.CurrentRegion.Column, 1, -1)
  11. Target.Offset(, c) = A.Offset(, c)
  12. Application.EnableEvents = True

  13. Else '·í¦³¤º®e³Q²MªÅ®É¤§³B²z

  14.    GoTo exitHandler

  15. End If

  16. exitHandler:
  17. Application.EnableEvents = True
  18. Exit Sub

  19. errHandler:
  20. 'MsgBox Err.Number & ": " & Err.Description
  21. GoTo exitHandler
  22. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD