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

Àx¦s®æ¤ñ¹ï«á.¾É¤J·Æ¹«¦ì¸m°ÝÃD2

Àx¦s®æ¤ñ¹ï«á.¾É¤J·Æ¹«¦ì¸m°ÝÃD2

¦U¦ì «e½ú¥ý¶i ¤j®a¦n

¦³2´£°Ý½Ð±Ð.¦]¦P¤@¤u§@ªí.¥i½Ð¤@°ÝÃD«ü¾É

°ÝÃD¤@)Àx¦s®æ¤ñ¹ï«á.¾É¤J·Æ¹«¦ì¸m
»Ý¨D:¦p¦ó±N·Æ¹«ÂI¶i"P2"Àx¦s®æ¤¤.¥H"P2"Àx¦s®æ¤¤¸ê®Æ¥h
         ¤ñ¹ï"K2"Àx¦s®æ²Ä1µ§¸ê®Æ«á.
        ·Æ¹«¦ì¸m¾É¤J©Ò¤ñ¹ï²Ä1µ§¸ê®Æ«á"EÄæ"Àx¦s®æ¤¤.
        ¨Ã±N¶Â¦â¦rÅéÂà´«¦¨¬õ¦â¦rÅé.Â÷¶}«á«ì´_¶Â¦â¦rÅé
        ¨Ò1)ÂI¶i1100601001«á.¤ñ¹ï"KÄæ".±o1100601001²Ä1µ§¸ê®Æ¦ì¸m¦b"E2".·Æ¹«¦ì¸m·|¦b"E2"
        ¨Ò2)ÂI¶i1100601005«á¤w¹ï"KÄæ".±o1100601005²Ä1µ§¸ê®Æ¦ì¸m¦b"E18".·Æ¹«¦ì¸m·|¦b"E18"
        ¨Ò3)ÂI¶i1100602005«á¤ñ¹ï"KÄæ".±o1100602005²Ä1µ§¸ê®Æ¦ì¸m¦b"E178".·Æ¹«¦ì¸m·|¦b"E178"

°ÝÃD¤G)¤p­p¬O§_¥i¦P¨B§ó·s
       ¤p­p"MÄæ"¤¤¥H¥Dµ{¦¡¾É¤J¸ê®Æ²£¥Í.¦]"EÄæ"¤¤»Ý­×§ï¼Æ¶q
       ¾É¦Ü"HÄæ"¤¤ÅܤƲ£¥Í¸ê®ÆÅܲ§
       .¦Ó¤p­p¬O¥Dµ{¦¡¥H³æ¸¹"KÄæ"Á`©M¬GµLªk¦P¨B§ó·s
       ²{¥H"H1"¤¤µ{¦¡©¹¤U©Ô.¥i±N"EÄæ"¤¤ÅܤƦӦP¨BÅܧó.
       ¦Ó¤p­p¬O¥H"KÄæ"³æ¸¹¤¤µ§¼Æ¬°Á`©M.¬GµLªk¦P¨B§ó·s

»Ý¨D: ¬O§_¥i¥ÎVBA¤ñ¹ï¤è¦¡.¥H¦Pµ§¼Æ"KÄæ"µ§¼Æ¦A¥H"HÄæ"µ§¼ÆÁ`©M§ó§ï¬°¤p­pÁ`©M


    ·PÁ¤j®a«ü¾É   

Àx¦s®æ¦ì¸m¾É¤J(1).zip (76.81 KB)
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

¦^´_ 1# BV7BW

Àx¦s®æ¦ì¸m¾É¤J v1.zip (77.85 KB)

TOP

¥»©«³Ì«á¥Ñ BV7BW ©ó 2021-6-24 06:11 ½s¿è

¦^´_ 2# singo1232001
«D±`·PÁ singo1232001 ¤j¤j «ü¾É
¹ï©ó²Ä¤@°ÝÃD«ü¾É
«D±`§¹¥þ¶êº¡ÜD©M©Ò»Ý.ÁÂÁ§A

PS:¬O§_¥i¦bÀ°ª`¸Ñ.§Ú¥i¥[§Ö²z¸Ñ singo1232001 ¤j¤j «ü¾É
  ÁÂÁÂ
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2021-6-24 06:45 ½s¿è
  1. Private Sub Worksheet_Change(ByVal Target As Range)   'Àx¦s®æ§ïÅܮɤ~·|IJµo target ´N¬O§A­ì¥ýÅܧó¸ê®ÆªºÀx¦s®æ(¤£¬O·s¿ï¨ìªº)
  2. If Target.Count > 1 Then Exit Sub '¿ï¨ì¨â®æ¥H¤W ±j¨îµ²§ô(¶]¤U¥h·|¥X°ÝÃD,¥»sub¤ºªº´£¨ìªº"¿ï¨ìªº®æ¤l"¬Ò¬°©|¥¼ÂIÀ»¤§«e¿ï¨ìªº®æ¤l)
  3. If Target.Column <> 5 Then Exit Sub '¿ï¨ì¤£¬O²Ä¤­Äæ ±j¨îµ²§ô(¶]¤U¥h·|¥X°ÝÃD)
  4. If Target.Row = 1 Then Exit Sub    '¿ï¨ì²Ä¤@¦C ±j¨îµ²§ô(¶]¤U¥h·|¥X°ÝÃD)
  5. If Target.Value = "" Then Exit Sub '¿ï¨ì®æ¤l¤ºµL¥ô¦ó¸ê®Æ ±j¨îµ²§ô(¶]¤U¥h·|¥X°ÝÃD)

  6. Target.Font.ColorIndex = xlAutomatic 'ÃC¦âÅܦ^¶Â

  7. Set s = ActiveSheet  '±N¤u§@ªíÁY¼g «á­±¨Ï¥ÎÁY¼g¤è«K
  8. On Error Resume Next '¤¹³\µo¥Í¿ù»~ ¤£¦A¸õ¥X´£¥Ü¿ù»~ĵ§iµøµ¡
  9. f = 0              
  10. f = s.Columns("A:M").Find("*", searchdirection:=2).Row  '¬d§ä³Ì«á¤@¦C , "*" §ä´MA~MÄæ«DªÅ¥Õ¦³¸ê®Æªº®æ¤l searchdirection:=2¥Ñ¤U©¹¤W¬d§ä
  11. If f = 0 Then Exit Sub                '¦pªGfÁÙ¬O0 ¥Nªí¨S¸ê®Æ ¤£¶]
  12. On Error GoTo 0             '¤£¤¹³\µo¥Í¿ù»~ ¦^´_¦¨´£¥Ü¿ù»~ĵ§iµøµ¡

  13. k = s.Cells(Target.Row, "K")  '¬d§ä¸Ó¦P¤@¦Cªº kÄæ­È
  14. x = 0  '¬ö¿ý¥[Á`¥Îªºx
  15. For i = 2 To f   '°j°é  (±q²Ä¤G¦C§ä¨ì ³Ì«á¤@¦C)
  16. If Str(k) = Str(s.Cells(i, "K")) Then x = x + s.Cells(i, "H")   '§ä¨ì¬Û¦Pªº²£«~½s¸¹ ±NHÄæ­È«ùÄò¥[Á`¨ìx
  17. Next
  18. '¨ì³oÃä¤w¸g¥[Á`§¹¥þ¦P¤@­Ó½s¸¹ªº¼Æ¶q
  19. Application.EnableEvents = False  'Ãö³¬¨Æ¥óIJµo ¥H§K³sÄò¼vÅT ¨ä¥LªºÄ²µo¨Æ¥ó selection »P change
  20. For i = 2 To f
  21. If Str(k) = Str(s.Cells(i, "K")) Then s.Cells(i, "M") = x     '§ä¨ì¬Û¦Pªº²£«~½s¸¹ ¨Ã¥B±N¥[Á`¦nªºx©ñ¨ì¦P¤@¦CªºMÄæ
  22. Next
  23. Application.EnableEvents = True '­«·s¶}±Ò¨Æ¥óIJµo
  24. Set s = Nothing  

  25. End Sub

  26. Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Àx¦s®æÂI¿ï®É¤~·|IJµo target ´N¬O§A·í¤UÂIªºÀx¦s®æ(·s¿ï¨ìªº)
  27. If Target.Count > 1 Then Exit Sub '¿ï¨ì¨â®æ¥H¤W ±j¨îµ²§ô(¶]¤U¥h·|¥X°ÝÃD)
  28. If Target.Row = 1 Then Exit Sub '¿ï¨ì²Ä¤@¦C ±j¨îµ²§ô(¶]¤U¥h·|¥X°ÝÃD)
  29. If Target.Value = "" Then Exit Sub '¿ï¨ì®æ¤l¤ºµL¥ô¦ó¸ê®Æ ±j¨îµ²§ô(¶]¤U¥h·|¥X°ÝÃD)

  30. If Target.Column <> 16 Then Exit Sub '¿ï¨ì¤£¬O²ÄPÄæ ±j¨îµ²§ô(¶]¤U¥h·|¥X°ÝÃD)

  31. Set s = ActiveSheet   '±N¤u§@ªíÁY¼g «á­±¨Ï¥ÎÁY¼g¤è«K
  32. On Error Resume Next '¤¹³\µo¥Í¿ù»~ ¤£¦A¸õ¥X´£¥Ü¿ù»~ĵ§iµøµ¡
  33. f = 0
  34. f = s.Columns("K").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole, searchdirection:=1).Row  'searchdirection:=1¥Ñ¤W©¹¤U¬d§ä ¥u§äKÄæ §ä¨ì¬Û¦Pªº½s¸¹
  35. If f = 0 Then Exit Sub '§ä¤£¨ì²Å¦X¶µ¥Ø ±j¨îµ²§ô(¶]¤U¥h·|¥X°ÝÃD)
  36. On Error GoTo 0

  37. Application.EnableEvents = False 'Ãö³¬¨Æ¥óIJµo
  38. s.Cells(f, "E").Select  'ÂI¿ï §ä¨ìªºÀx¦s®æ
  39. s.Cells(f, "E").Font.Color = -16776961 '¨Ã±NÃC¦â§ï¬°¬õ
  40. SendKeys "{F2}"   '¨Ã¶i¤J­×§ï¼Ò¦¡  f2¥»¨­¬°excelªº ¥\¯à
  41. Application.EnableEvents = True  '­«·s¶}±Ò¨Æ¥óIJµo
  42. Set s = Nothing
  43. End Sub
½Æ»s¥N½X
¦^´_ 3# BV7BW

TOP

¦^´_ 4# singo1232001

¦A¦¸ÁÂÁ  singo1232001 ¤j¤j
­è­è¥u´ú¸Õ°ÝÃD¤@).¨Sª`·N¨ì°ÝÃD¤G)¤]«D±`¦¨¥\
¤SÁÂÁ§AÀ°ª`¸Ñ.§Ú¦A²z¸Ñ§@ªk
ÁÂÁ singo1232001 ¤j¤j«ü¾É
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

SelectionChange ·|ÅýPÄæ¨S¿ìªk°µ§ó§ï,
¥i¥H§ï¥Î BeforeRightClick Âù«ö¥ªÁäIJµo

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2021-6-24 13:19 ½s¿è

¥t¤@¤èªk:
Xl0000045.rar (63.22 KB)

¡¯¢ÞÄæ²Ä¢±¦C¥H¤U¡AÂù«ö·Æ¹«¥ªÁä¡A°õ¦æ¬d¸ß¤Î­×§ï¡A¨Ã±N¦P¤@³æ¸¹½d³ò¥[¬õ¦â²Ê®Ø¡A
­×§ï¼Æ¶q®É¡A¢ÛÄæ²£¥Íªº¤½¦¡·|¦Û°Ê­pºâ¦X­È¡D¼Æ¶q¿é¤J¿ù»~®É¡A¨Ï¥Î´_­ì¥\¯à§Y¥i¡D
__¨Ï¥ÎCHANGE¨Æ¥ó¬OµLªk°µ´_­ìªº

¡¯­×§ï§¹¦¨«á¡A¦b¢Þ¢°Âù«ö¥ªÁä¡A¢ÛÄæ¦Û°ÊÅܦ¨­È¡]²M°£¤½¦¡¡^¡A¨Ã«ì´_¶Â¦â®Ø½u

TOP

¦^´_ 7# ­ã´£³¡ªL

­ã¤j½Ð±Ð¤@¤U¡A¨º¤@¥y¥Î·N¬°¦ó?·PÁÂ
Cancel = True

TOP

¦^´_ 8# samwang

Âù«ö¥ªÁä, ·|¶i¤JÀx¦s®æ¬°½s¿èª¬ºA(´å¼Ð°{Ã{), ©Ò¥H­n¥[ cancel=true, ´N¤£·|¤F,

µy§ï¤U, Àu¤ÆMÄæ¦X­p¤½¦¡:
Xl0000045-1.rar (63.47 KB)

TOP

¥»©«³Ì«á¥Ñ BV7BW ©ó 2021-6-24 20:21 ½s¿è

¦^´_ 9# ­ã´£³¡ªL
·PÁ ­ã´£³¡ªL ¦Ñ®v«ü¾É
¸g´ú¸Õ«á¹BÂබºZ
¤×¨ä"HÄæ"¤Î"MÄæ"§ï³yÜD©M²«K
¥i½Ð¦A¼W¦C¾É¤J«á·Æ¹«¦ì¸m±N¶Â¦â¦rÅéÂà´«¦¨¬õ¦â¦rÅé
Â÷¶}«á¤£¥Î«ì´_.¥H§Q±oª¾¦¹µ§¸ê®Æ¤w§ó§ï¹L

¥t¤@ÃD¥~ÃD.­ì¬O¤U¤@µo°Ý°ÝÃD
¦p¦ó«Ø¥ß·í¤é«È¤á­«½Æ¨¾§b°Ê§@¨Ã¥i¤¹³\
§Ú¥ý«Ø¥ßÀɮצA¤W¶Ç´£°Ý
¥ýÁÂÁ ­ã´£³¡ªL ¦Ñ®v

Àx¦s®æ¦ì¸m¾É¤J(1).zip (76.81 KB)

±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ·R¤£¬O­n¨D¹ï¤è¡A¦Ó¬O­n¥Ñ¦Û¨­ªº¥I¥X¡C
ªð¦^¦Cªí ¤W¤@¥DÃD