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

[µo°Ý] »ù®æ§R°£±i¼Æ¦Û°Ê§R°£

¦^´_  coafort


    ÁÂÁ«e½ú
«á¾ÇÂǦ¹©«¾Ç²ß¨ì Workbook.SheetChange ¨Æ¥ó
¥H¤U¬O¾Ç²ß¤è®×,½Ð«e½ú ...
Andy2483 µoªí©ó 2023-7-21 08:41


½Ð°Ý¤j¤j¡A¥Ø«eªº³]­p¬O³æ¤@§R°£¸òµÛ§R°£¤@­Ó
¦pªG¤@¦¸°é¿ï¦n´X­ÓµLªk¸òµÛ§R°£
½Ð°Ý¦³¿ìªk§ï¶Ü
ÁÂÁ¤j¤j

TOP

¦^´_ 31# coafort


    ÁÂÁ«e½ú¦A¦^´_,¤@°_¾Ç²ß
«á¾Ç¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò


Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Wi%, xA As Range, xR As Range, xP As Range, xI As Range
With Target
   On Error GoTo 99
   Set xA = Intersect(.Cells, Range([A1], ActiveSheet.UsedRange)).SpecialCells(4)
   On Error GoTo 0
   Wi = .Worksheet.Index
   If InStr("/2/3/", "/" & Wi & "/") Then Set xP = [A:A,AA:AA,AO:AO,P3:P23,T3:T23,P27:P40,T27:T40]
   If InStr("/4/5/7/8/", "/" & Wi & "/") Then Set xP = [A:A,AD:AD,AS:AS,Q3:Q23,V3:V23,Q27:Q40,V27:V40]
   Set xI = Intersect(xP, xA)
   If xI Is Nothing Then Exit Sub
   If Intersect(xI, .Cells) Is Nothing Then Exit Sub
   For Each xR In xI: xR.Offset(, 1).ClearContents: Next
99: End With
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 32# Andy2483

³ø§i¤j¤j¡A¤£¯à¥Î©O
ÁÂÁ¤j¤j

TOP

¦^´_ 33# coafort


    ÁÂÁ«e½ú¦A¦^´_
«á¾ÇÂǦ¹©«½Æ²ß¤è®×,¤è®×¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú°Ñ¦Ò


Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Wi%, xA As Range, xR As Range, xP As Range, xI As Range
'¡ô«Å§iÅܼÆ:Wi¬Oµu¾ã¼Æ,(xA,xR,xP,xI)³£¬OÀx¦s®æÅܼÆ
With Target
   On Error GoTo 99
   '¡ôµ{§Ç¹J¨ì¿ù»~´N¸õ¨ì¼Ð¥Ü 99ªº¦ì¸mÄ~Äò°õ¦æ
   Set xA = Intersect(.Cells, Range([A1], ActiveSheet.UsedRange)).SpecialCells(4)
   '¡ô¥OxAÅܼƬO ¥æ¶°®æ(IJµo®æ»P¦³¨Ï¥Î®æ)¸ÌªºªÅ¥Õ®æ
   Wi = .Worksheet.Index
   '¡ô¥OWiÅܼƬO IJµo¤u§@ªí¯Á¤Þ¸¹
   If InStr("/2/3/", "/" & Wi & "/") Then Set xP = [A:A,AA:AA,AO:AO,P3:P23,T3:T23,P27:P40,T27:T40]
   '¡ô¦pªGIJµo¤u§@ªí¯Á¤Þ¸¹¬O 2©Î3 ,´N¥OxPÅܼƬO[]¸ÌªºÀx¦s®æ
   If InStr("/4/5/7/8/", "/" & Wi & "/") Then Set xP = [A:A,AD:AD,AS:AS,Q3:Q23,V3:V23,Q27:Q40,V27:V40]
   '¡ô¦pªGIJµo¤u§@ªí¯Á¤Þ¸¹¬O 4.5.7©Î8 ,´N¥OxPÅܼƬO[]¸ÌªºÀx¦s®æ
   Set xI = Intersect(xP, xA)
   '¡ô¥OxIÅܼƬO ¥æ¶°®æ(xPÅܼƻPxAÅܼÆ)
   If xI Is Nothing Then Exit Sub
   '¡ô¦pªGxIÅܼƬO µLª«¥ó? True´Nµ²§ôµ{§Ç°õ¦æ
   If Intersect(xI, .Cells) Is Nothing Then Exit Sub
   '¡ô¦pªG¥æ¶°®æ(xIÅܼƻPIJµo®æ)¬O µLª«¥ó? True´Nµ²§ôµ{§Ç°õ¦æ
   For Each xR In xI: xR.Offset(, 1).ClearContents: Next
   '¡ô³]³v¶µ°j°é!¥OxRÅܼƬO xIÅܼƸ̪º¤@®æ,¥O¥k°¼¹j¾À®æ²M°£¤º®e
99: End With
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 34# Andy2483

ÁÂÁ¤j¤jªºÀ°¦£
¦ýÁÙ¬OµLªk¨Ï¥Î
§Ú§ï³o¼Ë
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Wi%, xA As Range, xR As Range, xP As Range, xI As Range
'¡ô«Å§iÅܼÆ:Wi¬Oµu¾ã¼Æ,(xA,xR,xP,xI)³£¬OÀx¦s®æÅܼÆ
With Target
   On Error GoTo 99
   '¡ôµ{§Ç¹J¨ì¿ù»~´N¸õ¨ì¼Ð¥Ü 99ªº¦ì¸mÄ~Äò°õ¦æ
   Set xA = Intersect(.Cells, Range([A1], ActiveSheet.UsedRange)).SpecialCells(4)
   '¡ô¥OxAÅܼƬO ¥æ¶°®æ(IJµo®æ»P¦³¨Ï¥Î®æ)¸ÌªºªÅ¥Õ®æ
   Wi = .Worksheet.Index
   '¡ô¥OWiÅܼƬO IJµo¤u§@ªí¯Á¤Þ¸¹
   If InStr("/2/3/6/", "/" & Wi & "/") Then Set xP = [A3:A233,AA3:AA233,AO3:AO233,P3:P23,T3:T23,P27:P40,T27:T40]
   '¡ô¦pªGIJµo¤u§@ªí¯Á¤Þ¸¹¬O 2©Î3 ,´N¥OxPÅܼƬO[]¸ÌªºÀx¦s®æ
   If InStr("/4/5/7/8/", "/" & Wi & "/") Then Set xP = [A3:A233,AD3:AD233,AS3:AS233,Q3:Q23,V3:V23,Q27:Q40,V27:V40]
   '¡ô¦pªGIJµo¤u§@ªí¯Á¤Þ¸¹¬O 4.5.7©Î8 ,´N¥OxPÅܼƬO[]¸ÌªºÀx¦s®æ
   Set xI = Intersect(xP, xA)
   '¡ô¥OxIÅܼƬO ¥æ¶°®æ(xPÅܼƻPxAÅܼÆ)
   If xI Is Nothing Then Exit Sub
   '¡ô¦pªGxIÅܼƬO µLª«¥ó? True´Nµ²§ôµ{§Ç°õ¦æ
   If Intersect(xI, .Cells) Is Nothing Then Exit Sub
   '¡ô¦pªG¥æ¶°®æ(xIÅܼƻPIJµo®æ)¬O µLª«¥ó? True´Nµ²§ôµ{§Ç°õ¦æ
   For Each xR In xI: xR.Offset(, 1).ClearContents: Next
   '¡ô³]³v¶µ°j°é!¥OxRÅܼƬO xIÅܼƸ̪º¤@®æ,¥O¥k°¼¹j¾À®æ²M°£¤º®e
99: End With
End Sub


½Ð°Ý¤j¤jÁÙ¦³­þ¨Ç»Ý­n§ï¶Ü¡H
«D±`ÁÂÁ¤j¤j

TOP

¦^´_ 35# coafort


    Andy¦Û¤v¼ÒÀÀªº´ú¸ÕÀÉ´ú¸ÕOK
¶Ç¤@¥÷½d¨ÒÀɤW¨Ó¬Ý¬Ý,½Ð¼F®`ªº«e½ú­ÌÀ°¦£
«á¾Ç¤u§@¦£,¼ÈµLªk±M¤ßÀ°¦£¸Ñ¨M,®e©öº|ªFº|¦èªº
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 36# Andy2483

ÁÂÁ¤j¤j¦Ê¦£¤¤À°¦£
¤j¤j¬O§_¤è«K´£¨Ñ¼g¦nªº½d¥»©O
«D±`·P®¦

TOP

¦^´_ 37# coafort



20230725.zip (14.35 KB)
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_  coafort
Andy2483 µoªí©ó 2023-7-25 12:55


·PÁ¦w­}¤j¤j
§Ú¸Õ¸Õ¬Ý

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C
ªð¦^¦Cªí ¤W¤@¥DÃD