|
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Count = 1 And Target <> "" Then
Application.EnableEvents = False
Target = matchSubject(Target.Text)
Application.EnableEvents = True
End If
End Sub
'匹配科目
Function matchSubject(val As String) As String
Dim arr, dic, i%, str$
arr = Range("i2:i" & Range("i65536").End(xlUp).Row)
Set dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
If arr(i, 1) Like "[[]*[]]*" Then
str = VBA.Split(arr(i, 1), "]")(0)
str = VBA.Split(str, "[")(1)
dic(str) = arr(i, 1)
End If
Next i
matchSubject = IIf(dic.exists(val), dic(val), "自定义错误信息") '自行修改文字内容
End Function
Book4.rar
(14.6 KB, 下载次数: 7)
|
|