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

[µo°Ý] excel VBA ¦Û°Ê§ó·s¿z¿ï°ÝÃD

¦^´_ 9# Andy2483
Dear Andy2483,
­è­è´ú¸Õ¨Ï¥ÎOK¡A·P®¦Åo¡C
½Ð°Ý¤@¤U¡A­Y¶µ¥Ø¶W¹L5­Ó¥H¤Wªº¸Ü¡A
¬O§_­×­q¤U¦C»yªk§Y¥i


   Dim Qx$, Af&, Sh$, S1$, S2$, S3$, S4$, S5$,  S6$,  S7$, i&
   Af = 32
   Sh = ""
   S1 = ""
   S2 = ""
   S3 = ""
   S4 = ""
   S5 = ""
  S6 = ""
   S7 = ""
Just do it.

TOP

¦^´_ 10# jsc0518


ElseIf InStr(Sh, Cells(i, "AF")) <> 0 Then
     GoTo 999
¦pªGÀx¦s®æCells(i, "AF")¬OÅã¥Üªº¥B¦bÁôÂêºÀx¦s®æ¸Ì¦³
´N¸õ¨ì 999Ä~Äò°õ¦æ
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 11# jsc0518


    «á­±¦³Ãö«Y¨ì¶µ¥Øªº³£­nÃþ±ÀEX:
ElseIf S6 = "" And InStr(S1 & S2 & S3 & S4 & S5, Cells(i, "AF")) = 0 Then
   S6 = Cells(i, "AF")
~~~

¦h¸Õ´X¦¸´N·|¤F!
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 11# jsc0518


    À³¸Ó¥i¥H³]­p¤£­­¨î¶µ¥Ø¼Æ¶q!
¦ý§Ú¤£·|! ÀR«Ý°ª¤â«ü¾É!
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 12# Andy2483
Dear Andy2483,
·PÁ±zªº¦^ÂлP±Ð¾É¡I
Just do it.

TOP

¦^´_ 13# Andy2483

¤F¸ÑÅo¡A¯uªº«Ü·PÁ§Aªº±Ð¾É¼Ú¡I
Just do it.

TOP

¦^´_ 16# jsc0518


    '¬ã¨s¤F3­Ó©«¤l«÷´ê¥X¤F ¤£­­¶µ¥Ø¼Æ¶qªº¤èªk
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   Dim Af&, Sh$, i&, Arr(), Brr(), K&, Dic As Object
   Set Dic = CreateObject("scripting.dictionary")
   Af = 32
   If .Column = Af And .Row >= 2 And .Count = 1 Then
      If ActiveSheet.FilterMode = True Then
         Arr = Range("AF2:AF" & ActiveSheet.UsedRange.Rows.Count)
         ReDim Brr(1 To UBound(Arr), 1 To 1)
         For i = 1 To UBound(Arr)
            If Rows(i).EntireRow.Hidden = True Then
               If Sh = "" Then
                  Sh = Cells(i, "AF")
                  ElseIf InStr(Sh, Cells(i, "AF")) = 0 Then
                     Sh = Sh & "," & Cells(i, "AF")
               End If
               ElseIf InStr(Sh, Cells(i, "AF")) <> 0 Then
                  GoTo 999
               ElseIf Dic.exists(Arr(i, 1)) Then
                  Dic(Arr(i, 1)) = ""
                  K = K + 1
                  Brr(K, 1) = Arr(i, 1)
            End If
            
999
         Next
         If InStr(Sh, .Value) <> 0 Then
            .Rows(.Count).EntireRow.Hidden = True
            ElseIf K > 0 Then
               Selection.AutoFilter Field:=Af, Criteria1:=Brr, Operator:=xlFilterValues
               ActiveSheet.AutoFilter.ApplyFilter
         End If
      End If
   End If
End With
End Sub
'²q´ú¥Í²£¬yµ{¬O «Ý®Æ>«Ý¥Í²£>¥Í²£¤¤>µ²§å  ¯S®íª¬ªp ¾÷¥x²§±`or¼È°± ¥H¤U¸gÅç¨Ñ°Ñ¦Ò
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target
   '¦bAFÄæÀx¦s®æ¥H·Æ¹«¥ªÁä§Ö«ö¨â¦¸>>¨Ì·Ó¬yµ{ §ïÅܬyµ{¶i«×
   If .Column = 32 And .Row >= 2 Then
      If .Value = "«Ý®Æ" Then
         .Value = "«Ý¥Í²£"
         ElseIf .Value = "«Ý¥Í²£" Then
            .Value = "¥Í²£¤¤"
         ElseIf .Value = "¥Í²£¤¤" Then
            .Value = "µ²§å"
         ElseIf .Value = "µ²§å" Then
            MsgBox "?"
         ElseIf .Value = "¾÷¥x²§±`" Then
            .Value = "¥Í²£¤¤"
         ElseIf .Value = "¼È°±" Then
            .Value = "¥Í²£¤¤"
      End If
      Cancel = True
   End If
   '¦bAGÄæÀx¦s®æ¥H·Æ¹«¥ªÁä§Ö«ö¨â¦¸>>§ïÅܬyµ{¯S§Oª¬ªp(¾÷¥x²§±`)
   If .Column = 33 And .Row >= 2 Then
      If .Cells(1, 0) = "¥Í²£¤¤" Then
         .Cells(1, 0) = "¾÷¥x²§±`"
      End If
      Cancel = True
   End If
   '¦bAF1Àx¦s®æ¥H·Æ¹«¥ªÁä§Ö«ö¨â¦¸>>¸Ñ°£¥þ³¡Äæ¦ì¿z¿ï
   If .Address = "$AF$1" Then
      If ActiveSheet.FilterMode = True Then
         ActiveSheet.ShowAllData
      End If
      Cancel = True
   End If
End With
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
With Target
   '¦bAFÄæÀx¦s®æ«ö·Æ¹«¥kÁä>>ª½±µÀ°¦bAFÄæ¿z¿ï·í®æªº¶µ¥Ø
   'EX: ¦b¨S¦³¿z¿ïªº±¡ªp¤U ¦bAF2Àx¦s®æ«ö·Æ¹«¥kÁä,¦pªGAF2ªº¤å¦r¬O "¥Í²£¤¤" ´NÀ°¦bAFÄæ¿z¿ï "¥Í²£¤¤"
   If .Column = 32 And .Row >= 2 And .Count = 1 Then
      If ActiveSheet.FilterMode = True Then
         ActiveSheet.ShowAllData
      End If
      Selection.AutoFilter Field:=32, Criteria1:=.Value, Operator:=xlFilterValues
      Cancel = True
   End If
   '¦bAGÄæÀx¦s®æ«ö·Æ¹«¥kÁä>>§ïÅܬyµ{¯S§Oª¬ªp(¼È°±)
   If .Column = 33 And .Row >= 2 Then
      If .Cells(1, 0) = "¥Í²£¤¤" Then
         .Cells(1, 0) = "¼È°±"
      End If
      Cancel = True
   End If
End With

°Ñ¦Ò!
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 17# Andy2483
Dear Andy2483,
¤U¤È¦n¡I·PÁ±zªº¼ö¤ß¦^´_¼Ú¡A§Ú¦A¨Ó´ú¸Õ¬Ý¬Ý¡I
Thank you so much. ^^
Just do it.

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2021-7-25 21:47 ½s¿è

¦^´_ 18# jsc0518

³o°Q½×¦êÁÙ¬¡µÛªü¡A¬Ý¤F¤@¤U¡A·Pı·d½ÆÂøºO~~

¥u­n·Q¿ìªk°O¿ý "¨Ï¥ÎªÌ¤£­nªº" ¿ï¶µ¡A¦A­«·s³]©w¿z¿ï±ø¥ó´N¦n¤F§r~

§Ë¤@­Óµ¹§A­Ì°Ñ¦Ò¬Ý¬Ý§a¡A©ñ±ó­ì¥»¤â°Ê¥Î²Ä¤@¦C¿z¿ïªº¥\¯à¡A¥Îªí³æ¿z¿ï


1.¥ô¤@ÄæÂI¿ï¨â¤U¥ªÁä¸õ¥Xªí³æ
2.ªí³æ·|¦Û°Ê§ì²Ä2¦Cªº "¸ê®Æ¿z¿ï²M³æ"¡A¨Ã¨q¥X¨Óµ¹§A¿ï¾Ü
3.§â"­nªº"¥´¤Ä¡A«ö½T©w§Y¥i§¹¦¨¿z¿ï (µ{¦¡·|¦Û°Ê°O¿ý§A"¤£­n"ªº)
4.°²³]¸ÓÄæ¸ê®Æ¨S¦³ItemA¡A¦ý¬O²M³æ¦³¥´¤Ä¡A¸ÓÄæ¦h¤F¤@µ§¸ê®ÆItemA¡A¤]¤£·|¦Û°ÊÁôÂÃ
5.¤ä´©ÂX¥RNÄæ¡A¤£­­"AF"Äæ¡A¦ý¬O­Y¸ÓÄæ¨S¦³"¸ê®Æ¿z¿ï²M³æ"¡Aªí³æ¤W¤£·|¦³ªF¦èµ¹§A¤Ä
6.³]©w±ø¥ó¥i¥H¬ö¿ý¨ìÀÉ®×Ãö³¬¡A¥ç§YÀÉ®×Ãö³¬¦A¶}±Ò¡A»Ý­n­«·s³]©w
7.¥Ø«e¥u¤ä´©1­Óªí®æ¡A­Y­n¤ä´©¦hªí®æ¡A½Ð¦Û¦æÂX¥R¼Ò²Õ"Àx¦sList"¡A¨ú¤£¦P¼Ò²Õ¦WºÙ§Y¥i
   Ex:"ªí2Àx¦sList"¡B"ªí3Àx¦sList" ¡A¦p¦¹¤~¯à°O¿ý¤£¦Pªí®æªº ¿z¿ï±ø¥ó(¤£­nªº)
8.ªí³æ½d³ò¤j·§¤ä´©30­Ó¿ï¶µ¡A¦A¦hÀ³¸ÓÅã¥Ü¤£¥X¨Ó


Error_ªüÀs.rar (34.21 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2021-7-26 00:16 ½s¿è

¦^´_ 1# jsc0518


¥ý»¡©ú¤@¤U
§Ú¼gªº«Ü¤ßµê
¥i¯à·|¦³¤@°ï©_©Çbug
´N·í§@°Ñ¦Ò ª±¬Ý¬Ý

§Ú»{¬°³Ì¦³¥i¯à¥X²{ªºbug ¦b©ó §ó¤§«áªº¹B¥Î
¤]´N¬O·í§A§â¯u¹ê¸ê®Æ©ñ¤J
©ÎªÌ¾Þ§@¤@¨Ç½d¨Ò¥~ªº¸ê®Æ
¥i¯à´N·|²£¥Í¥¼ª¾bug
¤×¨ä¬O afÄæ ²M°£¸ê®Æ ©ÎªÌ ¬ðµM·s¼W¸ê®Æ
¸ê®Æ°O±o­n³Æ¥÷ ¨Ã¥B ¥ý¦æ¹ê»Ú´ú¸Õ ¦UºØ¾Þ§@ ³á

¥t¥~¸Ì­±¦³¤@¨Ç²ÖÂتºµ{§Ç §Ú¨S±þ±¼ ¤ñ¦prdÅܼÆ
¦]¬°§Ú·Q»¡ ¥i¯à·|¦³bug ¤£´±¶Ã§R ©ÎªÌ¤§«á­×§ï­n¥Î¨ì

error v1.zip (27.02 KB)

TOP

        ÀR«ä¦Û¦b : §g¤l¦p¤ô¡AÀH¤è´N¶ê¡AµL³B¤£¦Û¦b¡C
ªð¦^¦Cªí ¤W¤@¥DÃD