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

[µo°Ý] ÃC¦â§P§O

¦^´_ 20# samwang

ÁÂÁ¤j¤j

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-12-30 08:10 ½s¿è

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


    ÁÂÁ«e½ú
¤µ¤ÑÂǦ¹©«½m²ß°}¦C»P¦r¨å,±N®æ¦¡¤Æ±ø¥ó§ï¬°VBA,½Ð«e½ú¦A«ü¾É,ÁÂÁ«e½ú

°õ¦æ«e:


°õ¦æµ²ªG:


¤u§@ªí¼Ò²Õ:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target
     If .Row < 3 Or .Value = "" Then Exit Sub
     If .Column Mod 4 <> 1 Then Exit Sub
     Cancel = True
     Call Åܦr¦â_¦h­Ó¦PªÑ¦W
     
     If Y(.Value & "|") > 1 Then Y(.Value).Interior.ColorIndex = 4
     Set Y = Nothing
     Set Brr = Nothing
End With
End Sub

Module1:
Option Explicit
Public Brr, Y
Sub Åܦr¦â_¦h­Ó¦PªÑ¦W()
Dim C&, R&
Set Y = CreateObject("Scripting.Dictionary")
ActiveSheet.UsedRange.Offset(2).Font.ColorIndex = 1
ActiveSheet.UsedRange.Offset(2).Interior.ColorIndex = xlNone
Brr = ActiveSheet.UsedRange
Set Y(1) = [B1]
For C = 1 To UBound(Brr, 2) Step 4
   For R = 3 To UBound(Brr)
      Y(Brr(R, C) & "|") = Y(Brr(R, C) & "|") + 1
      If Y(Brr(R, C) & "|") = 1 Then
         Set Y(Brr(R, C)) = Cells(R, C)
         GoTo PASS
      End If
      If Y(Brr(R, C) & "|") = 2 Then
         Set Y(1) = Union(Y(1), Y(Brr(R, C)))
      End If
      Set Y(1) = Union(Y(1), Cells(R, C))
      Set Y(Brr(R, C)) = Union(Y(Brr(R, C)), Cells(R, C))
      
PASS:
   Next
Next
Y(1).Font.ColorIndex = 5
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ coafort ©ó 2023-3-30 10:12 ½s¿è
¦^´_  coafort

®æ¦¡±ø¥ó+vba
1) ­«ÂÐ--ÂŦâ¦r
2) ¹ï¦³­«ÂЪºªÑ²¼¦WºÙÂùÀ»¥ªÁä, ©Ò¦³¬Û¦Pªº¼Ð²L¬õ©³
...
­ã´£³¡ªL µoªí©ó 2021-10-31 15:45


­ã¤j¤j±z¦n
§Ú¼W¥[E J O T³o¨ÇÄæ¦ì
Åܦâ¥\¯à´N²§±`¤F
½Ð°Ý­n¦p¦ó§ï©O¡H
ÁÂÁ­ã¤j¤j

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-3-30 16:28 ½s¿è

¦^´_ 23# coafort


    ÁÂÁ½׾Â,ÁÂÁ«e½ú
«á¾Ç¼ÒÀÀ±¡¹Ò,¾Ç²ßVBAÀx¦s®æ¶°.IJµo»P¦r¨åªº¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò
Xl0000797_20230330_3.zip (34.73 KB)

°õ¦æ«e:


¿é¤J«á°õ¦æµ²ªG:


¤u§@ªí¼Ò²Õ:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target
     If .Row < 3 Or .Value = "" Or .Count > 1 Then Exit Sub
     If .Column Mod 5 <> 1 Then Exit Sub
     Cancel = True
     Call Åܦr¦â_¦h­Ó¦PªÑ¦W
     
     If Y(.Value & "|") > 1 Then Y(.Value).Interior.ColorIndex = 4
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   If .Row < 3 Or .Count > 1 Then Exit Sub
   If .Column Mod 5 <> 0 Then Exit Sub
   Call Åܦr¦â_¦h­Ó¦PªÑ¦W
   
   If Y(.Offset(0, -4) & "|") > 1 Then
      Y(.Offset(0, -4) & "").Interior.ColorIndex = 4
      Application.EnableEvents = False
      Y(.Offset(0, -4) & "/").Value = .Value
      Application.EnableEvents = True
      Application.Goto Y(.Offset(0, -4) & "/")
   End If
End With
End Sub

Module1:
Option Explicit
Public Y
Sub Åܦr¦â_¦h­Ó¦PªÑ¦W()
Dim Brr, Crr, C&, i&, X&, xR, R&, T, V, Z, Ad
Set Y = CreateObject("Scripting.Dictionary")
ActiveSheet.UsedRange.Offset(2).Font.ColorIndex = 1
ActiveSheet.UsedRange.Offset(2).Interior.ColorIndex = xlNone
Brr = ActiveSheet.UsedRange
Set Y(1) = [B1]
For C = 1 To UBound(Brr, 2) Step 5
   For R = 3 To UBound(Brr)
      Y(Brr(R, C) & "|") = Y(Brr(R, C) & "|") + 1
      If Y(Brr(R, C) & "|") = 1 Then
         Set Y(Brr(R, C)) = Cells(R, C)
         Set Y(Brr(R, C) & "/") = Cells(R, C + 4)
         GoTo PASS
      End If
      If Y(Brr(R, C) & "|") = 2 Then
         Set Y(1) = Union(Y(1), Y(Brr(R, C)))
      End If
      Set Y(1) = Union(Y(1), Cells(R, C))
      Set Y(Brr(R, C)) = Union(Y(Brr(R, C)), Cells(R, C))
      Set Y(Brr(R, C) & "/") = Union(Y(Brr(R, C) & "/"), Cells(R, C + 4))
      
PASS:
   Next
Next
Y(1).Font.ColorIndex = 5
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_  coafort


    ÁÂÁ½׾Â,ÁÂÁ«e½ú
«á¾Ç¼ÒÀÀ±¡¹Ò,¾Ç²ßVBAÀx¦s®æ¶°.IJµo»P¦r¨åªº¤è®×¦p¤U,½Ð«e½ú°Ñ ...
Andy2483 µoªí©ó 2023-3-30 16:01


ÁÂÁ¤j¤j
¦pªG³æ´N­È§Q²v³¡¤ÀªºVB¬O­þ¤@¬q©O
ÁÂÁÂ

TOP

¦^´_ 25# coafort


    ÁÂÁ«e½ú¦^´_
24¼ÓÂŦâ¦r³¡¤À¬OÃö©ó´Þ§Q²v,¸ò¤§«e¥\¯à²V¦A¤@°_¤F
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_  coafort


    ÁÂÁ«e½ú¦^´_
24¼ÓÂŦâ¦r³¡¤À¬OÃö©ó´Þ§Q²v,¸ò¤§«e¥\¯à²V¦A¤@°_¤F
Andy2483 µoªí©ó 2023-3-30 16:31



ÁÂÁ¤j¤j
¤£¦n·N«ä¤j¤j
¯à§_¿W¥ß­È§Q²v¨º­Ó³¡¤À¶Ü
·P®¦

TOP

¦^´_ 27# coafort


    ¨â­Ó¥\¯à¤¬¤£¤z¯A,¦³¤°»ò°ÝÃD¶Ü?
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_  coafort


    ¨â­Ó¥\¯à¤¬¤£¤z¯A,¦³¤°»ò°ÝÃD¶Ü?
Andy2483 µoªí©ó 2023-3-30 16:46


¦]¬°·Q°µ¨âºØ¤£¦Pª©¥»
ÁÂÁ¤j¤j

TOP

¦^´_ 29# coafort


    ÁÂÁ«e½ú¦^´_
«Øij«e½ú¤W¶Ç²{¦æ®æ¦¡¤Æ±ø¥ó5Ä檩¥»½d¨Òµ¹¨ä¥L«e½úÀ°¦£¸Ñ¨M¤è®×¡A
«á¾Ç¹ï®æ¦¡¤Æ±ø¥óµÛ¾¥¤£²`¡A½Ð³o¤è­±¼F®`ªº«e½úÀ°¦£¡AÁÂÁ¦U¦ì«e½ú
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : Ä@­n¤j¡B§Ó­n°í¡B®ð­n¬X¡B¤ß­n²Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD