本帖最后由 长颈鹿 于 2013-5-8 22:14 编辑
下面是F列的,g、h....等列的代码如何写,谢谢
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim arr, Diction, i!, num
Set Diction = CreateObject("scripting.dictionary")
arr = Sheets("表2").Cells(4, 6).Resize(7, 2)
For i = 1 To UBound(arr)
Diction(arr(i, 1)) = arr(i, 2)
Next i
If InStr(1, Target.Address, "F") > 0 Then
num = Target
Application.EnableEvents = False
If Diction.exists(num) Then
Target = Diction(num)
Else
Target = "无此名称"
End If
Application.EnableEvents = True
End If
End Sub
- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim arr, Diction, i!, num
- '字典对象
- Set Diction = CreateObject("scripting.dictionary")
- 'F4单元格开始的7行2列读入数组
- arr = Sheets("表2").Cells(4, 6).Resize(7, 2)
- For i = 1 To UBound(arr)
- '数据读入字典
- Diction(arr(i, 1)) = arr(i, 2)
- Next i
- '判断地址中是否包含F1
- '这时有BUG,当是FA-FZ列全,就中招了
- If InStr(1, Target.Address, "F") > 0 Then
- num = Target
- '关闭事件响应,避免事件死循环
- Application.EnableEvents = False
- '在字典中查找内容对应的值(居住性质)
- If Diction.exists(num) Then
- '另外这里应该也错了,Target.Offset(, 1) = Diction(num)
- Target = Diction(num)
- Else
- '单元格内写入无此名称
- Target = "无此名称"
- End If
- '打开事件响应
- Application.EnableEvents = True
- End If
- End Sub
复制代码
|