|
要求:
L列输入需要时,M列出现对应的公司名。
M列输入公司名(可以不全),L列出现序号,M列出现公司全名。
基本可以实现,但代码会出现卡顿,求大神出手优化。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MK%, Arr, Brr, i!, j!
Application.EnableEvents = True
If Target.Row < 3 Then Exit Sub
If Target.Column <> 12 And Target.Column <> 13 Then Exit Sub
If Target.Count > 1 Then Exit Sub
Select Case Target.Column
Case 12
MK = 500
Arr = Sheet5.Range("A1:B500")
For i = 1 To 500
If Arr(i, 1) = Target Then Target.Offset(, 1) = Arr(i, 2)
Next i
Case 13
MK = 500
Arr = Sheet5.Range("A1:B500")
If Target <> "" Then
For j = 1 To MK
If InStr(Arr(j, 2), Target) Then Target.Offset(, 0) = Arr(j, 2): Target.Offset(, -1) = Arr(j, 1): Exit For
Next j
End If
Case Else
End Select
End Sub
|
|