|
本帖最后由 278059169 于 2016-7-4 10:42 编辑
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Column = 1 And Target.Row > 2 Then
Application.EnableEvents = False
Target.Offset(0, 1) = Sheets(2).Range("a:a").Find(Target, , , xlWhole).Offset(0, 1)
Application.EnableEvents = True
ElseIf Target.Column = 2 And Target.Row > 2 Then
Application.EnableEvents = False
Target.Offset(0, -1) = Sheets(2).Range("b:b").Find(Target, , , xlWhole).Offset(0, -1)
Application.EnableEvents = True
End If
End If
End Sub
表2的A列是数组000001
B列是名称比如平安银行
在表1的A或B列输入对应的数字或名字,就自动引用表2的对应项到表1的AB列
现在的问题是,随便在表1的A或B列输入错误值后,表格就再也不工作了,只能不保存退出重新打开
能不能输入错误后,提示“没有该股票,请核对后再输入”而后表格是正常工作的
帮你改了一下代码:
测试通过! - Private Sub Worksheet_Change(ByVal Target As Range)
- On Error GoTo Err_Handle
- If Target.Count = 1 Then
- If Target.Column = 1 And Target.Row > 2 Then
- Application.EnableEvents = False
- Target.Offset(0, 1) = Sheets(2).Range("a:a").Find(Target, , , xlWhole).Offset(0, 1)
- Application.EnableEvents = True
- ElseIf Target.Column = 2 And Target.Row > 2 Then
- Application.EnableEvents = False
- Target.Offset(0, -1) = Sheets(2).Range("b:b").Find(Target, , , xlWhole).Offset(0, -1)
- Application.EnableEvents = True
- End If
- End If
- Exit Sub
- Err_Handle:
- Application.EnableEvents = True
- MsgBox ("没有该股票,请核对后再输入")
- Target = ""
- Target.Select
- End Sub
复制代码
|
|