²Å¦X¨â­Ó¥H¤Wªº±ø¥ó¡A¨ÃÅã¥Ü¬ÛÃö¸ê°T

013160

2020-11-14 16:23

»ù®æ.rar (14.4 KB)

¤U¸ü¦¸¼Æ: 11

2020-11-14 16:24

¦U¦ìª©¤j±z¦n¡A
½Ð°Ý¦pªG­n²Å¦X¨â­Ó¥H¤W±ø¥óªºÅã¥Ü¥X¬ÛÃö¸ê°T¤½¦¡À³¦p¦ó³]©w?
EX¡G¿é¤J«~¦W³W®æ©M¤Ø¤o¡@¦b¤U¤èÅã¥Üµ±¶Z¡þ»ù®æ¡D¡D¡D¸ê°T

hcm19522

https://blog.xuite.net/hcm19522/twblog/589462664

013160

¦^´_ 2# hcm19522

¤£¦n·N«ä¥i¥H¸ÑÄÀ¤@¤U¤½¦¡¶Ü?

hcm19522

¦^´_ 3# 013160


    §PÂ_¤G±ø¥ó²Å¦X ¨ú¦C¦ì¸m ¦A±q³Ì¤p¦ì¸m¨ú¸ê®Æ  ¥k©Ô¸õ°Ê¦¡ «D¨Ì§Ç¨ú¸ê®Æ ©Ò¥H¥ÎCHOOSE(COLUMN(A1)

Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß¦bÀx¦s®æ¦Û°Ê²£¥Í»Ý¨D²M³æ»P¦¸²M³æ,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
¿ï¨ú[K2]Àx¦s®æ²£¥Í²M³æ:

2024-1-17 15:46

[K2]¿ï¨ú²M³æ«á¦b[M2]²£¥Í¦¸²M³æ:

2024-1-17 15:47

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Brr, Z, i&
With Target
   If .Address(0, 0) = "K2" Then
      Brr = Range([C2], [C65536].End(3))
      Set Z = CreateObject("Scripting.Dictionary")
      For i = 1 To UBound(Brr)
         If Trim(Brr(i, 1)) <> "" Then Z(Trim(Brr(i, 1))) = ""
      Next
      With .Validation
        .Delete
        If Z.Count > 0 Then .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Join(Z.KEYS(), ",")
        Set Z = Nothing: Brr = Empty
    End With
   End If
End With

End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Brr, Z, i&
With Target
   If .Address(0, 0) = "K2" Then
      Brr = Range([D2], [C65536].End(3))
      Set Z = CreateObject("Scripting.Dictionary")
      For i = 1 To UBound(Brr)
         If Brr(i, 1) = .Value And Trim(Brr(i, 2)) <> "" Then Z(Trim(Brr(i, 2))) = ""
      Next
      With [M2].Validation
        .Delete
        If Z.Count > 0 Then .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Join(Z.KEYS(), ",")
        Set Z = Nothing: Brr = Empty
    End With
   End If
   If .Address(0, 0) = "M2" Then Call ¦C¥X©ú²Ó
End With
End Sub

Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C,±N²Å¦X±ø¥óªº¸ê®Æ©ú²Ó¦C¥X,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:

2024-1-18 09:21

¿ï¨ú²M³æ«á°õ¦æµ²ªG:

2024-1-18 09:22

Sub ¦C¥X©ú²Ó()
Dim Brr, i&, K$, M$, T3$, T4$, A, N&, j%
Brr = Range([I2], [B65536].End(3)(1, 0))
Intersect([J:N], ActiveSheet.UsedRange).Offset(4).ClearContents
K = Trim([K2]): M = Trim([M2]): A = [{5,6,9,8,2}]
For i = 2 To UBound(Brr)
   T3 = Trim(Brr(i, 3)): T4 = Trim(Brr(i, 4))
   If T3 = K And T4 = M Then
      N = N + 1
      For j = 1 To 5: Brr(N, j) = Brr(i, A(j)): Next
   End If
Next
If N = 0 Then Exit Sub Else [J5].Resize(N, 5) = Brr
Brr = Empty
End Sub