Andy2483

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


    ÁÂÁ«e½ú
¾Ç¤½¦¡«Ü¤£Â²³æ!À´VBA¸Ñªk¤£¤@©w·|¤½¦¡¸Ñªk,«á¾Ç¾q¶w,¾Ç¤½¦¡§Æ±æ¶Ô¯à¸É©å
¥H¤U¬O«á¾Ç¾Ç²ß¥HVBAªº¤è¦¡³B²z,Àµ½Ð«e½ú¦A«ü¾É,ÁÂÁÂ
¤£¥Î(Application.EnableEvents = False)ºÉ¤O¨¾°ô¦AIJµo,¤£ª¾¤°»ò­ì¦]ÁÙ¬Oµo¥Í¦AIJµo¤F,­þ¸Ì¿ù¤F??

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Arr, V$, N&, A$, i&
With Target
  If InStr(.Address, "$Q$9") Or .Count > 1 Then
      Exit Sub
   End If
   If .Address = "$Q$8" Then
      V = .Value
      Arr = Sheets("«È¤á°ò¥»¸ê®Æ").Range([«È¤á°ò¥»¸ê®Æ!D1], [«È¤á°ò¥»¸ê®Æ!C65536].End(3))
      For i = 2 To UBound(Arr)
         If Arr(i, 1) & Arr(i, 2) & "/" Like "*" & V Then
            A = Split(V, "*")(0)
            A = Format(InStr(Arr(i, 1) & Arr(i, 2) & "/", A), "00|")
            N = N + 1
            Arr(N, 1) = A & Arr(i, 1) & "_" & Arr(i, 2)
         End If
      Next
      Range([Q9], Cells(Rows.Count, "Q").End(3)(2)) = ""
      If N = 0 Then Exit Sub
      With .Item(2, 1).Resize(N, 1)
         .Value = Arr
         If N > 1 Then
            [T:W].EntireColumn.Hidden = False
            .Sort Key1:=.Item(1), Order1:=xlAscending, Header:=xlNo
            [T:W].EntireColumn.Hidden = True
         End If
         .Replace "*|", ""
      End With
   End If
End With
End Sub