Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$Q$8" Then
'↑如果編輯內容的儲存格位址是 "$Q$8"
If InStr(.Value, "*") Then
'↑如果編輯的結果內容裡包含"*"字元
[S8] = Split(.Value, "*")(1): [Q8] = Split(.Value, "*")(0)
'↑if條件成立就令[S8]儲存格是 以"*"字元分割[Q8]儲存格後右邊的字串,
'令[Q8]儲存格是 以"*"字元分割[Q8]儲存格後左邊的字串
'所以以[Q8]儲存格輸入 "永豐*28" 為例:[S8]="28",[Q8]="永豐",
'另外以 "永豐*" 為例:[S8] = "" , [Q8]="永豐"
End If
End If
End With
End Sub
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
