[µo°Ý] ½Ð°ÝExcel¤U©Ô¿ï³æ,¿ï¾Ü«á¦Û°ÊÂà¨ì©Ò»Ý­¶­±ªºVBA¸Ó¦p¦ó¼g

lky12345

½Ð°Ý¦bExcelÀx¦s®æ¤¤,³]©w¤U©Ô¿ï³æ,¦A¸ÓÀx¦s®æ¿ï¾Ü«á¦Û°ÊÂà¨ì©Ò»Ý­¶­±«ü©wÄæ¦ìªºVBA¸Ó¦p¦ó¼g?
¦b(sheet1)AÄ椤ªºÀH¾÷Äæ¦ì¤W,¥u­n¤U©ÔÂI¿ï"¦C¦L"«á«hÂà¦Ü(sheet2)­¶­±«ü©wÄæ¦ì,
¦p(sheet1)AÄ椤¦³¿ï¾Ü2­Ó¦C¦]®É,«h¥X²{msg box´£¿ô,
«ö¤U½T©w«á.¦^¨ì­ì¸ê®Æ®æ¤W
ÁÂÁÂ

¤U¸ü (36.5 KB)

2024-1-3 18:10

¤U¸ü (37.49 KB)

2024-1-3 18:10

lky12345

¦^´_ 9# liaovba
¦nªº,ÁÂÁ¤j¤jªº«ü¾É

liaovba

¦^´_ 3# lky12345


      N = Application.CountIf([A:A], "¦C¦L")  这¨½ªºN¬O读¨ú¤F¾ã¦C¡A¦pªG§A·Q«ü©w­S围¡A¥i¥H§ï变这¨½ªºA:Aªº­È¡A¦p¦ó§ï为A1:A10

lky12345

¦^´_ 7# Andy2483
¤j¤j§A¦n
¬OÂà¨ìsheet2«á´N¥ÑÂI¿ï¶i¦æ©Ò»Ý­nªº¦C¦L
ÁÂÁÂ
«Ü·PÁ¦³±zªº¨ó§U,³o¼Ëªº¥\¯à¤w¸g«Ü¦n¤F,ÁÂÁÂ

Andy2483

¦^´_ 6# lky12345

Sheets(2).Activate
³o¬O¸õ¨ì²Ä2¤u§@ªíªº·N«ä
¨ãÅ骺­n¸õ¨ì²Ä2¤u§@ªí°µ¤°»ò¨Æ¡H¥i¦A¨ãÅ骺»¡©ú©Î¤W¶Ç½d¨ÒÀÉ

lky12345

¦^´_ 5# lky12345
¤j¤j§A¦n
¤£¦n·N«ä,¨Ï¥Î«á¦³­Ó°ÝÃD´N¬O¦pªGAÄ椤¦³Åã¥Ü"¦C¦L"·Æ¹«¥u­nÂI¨ìAÄ椤¥ô¦ó¦ì¸m,«h·|¦Û°ÊÂà¨ìsheet2
½Ð°Ý³o¦³¿ìªk§ïµ½¶Ü?
ÁÂÁÂ

lky12345

¦^´_ 4# Andy2483
ÁA¸Ñ¤F,·PÁ¤j¤jªº«ü¾É,ÁÂÁÂ

Andy2483

¦^´_ 3# lky12345


    ³oºI¹Ïµe­±¬O¦b¤@¯ë¼Ò²Õ
³oµ{¦¡½X¬OIJµo¨Æ¥ó¡A©Ò¥H­n©ñ¤J¤u§@ªí¼Ò²Õ¡AÀx¦s®æ­È§ïÅܮɷ|¦Û°Ê°õ¦æ
©Ò¥H½Ð¦bVBA¸Ì±N´å¼Ð²¾¨ì±ý´Ó¤Jªº¤u§@ªí¤U§Ö«ö¨â¤U¡A±Nµ{¦¡½X´Ó¤J«á´N¥i¥H¨ìªí®æ´ú¸Õ¤@¤U

lky12345

¦^´_ 2# Andy2483
·PÁ¤j¤jªº¦^ÂÐ,¦]¬°­Ó¤H¹ïVBA¤]¤£¬O«Ü¼ô±x
©Ò¥HÁٽФj¤j¨ó§UÀ°¦£,ÁÂÁÂ
²{¦bªº°ÝÃD¬O§Ú¶ñ¤Jvba¤¤«o¥X²{¥H¤U°T®§
½Ð°Ý¬O­þÃä¥X¿ù,½Ð¦A«ü¾É,ÁÂÁÂ

¤U¸ü (55.7 KB)

2024-1-4 18:23

Andy2483

¦^´_ 1# lky12345


    ÁÂÁ½׾Â,ÁÂÁ«e½úµoªí¦¹¥DÃD
«á¾ÇÂǦ¹©«½m²ßIJµo»PCountIf()¨ç¼Æ,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

¤u§@ªí¼Ò²Õ:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim N%
With Target
   If .Column = 1 And .Rows.Count = 1 Then
      N = Application.CountIf([A:A], "¦C¦L")
      If N > 1 Then MsgBox "AÄ榳 " & N & " ­Ó¦C¦L": Exit Sub
      If N = 1 Then
         Sheets(2).Activate
         '~~Âà¦Ü(sheet2)­¶­±«ü©wÄæ¦ì
      End If
   End If
End With
End Sub