Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$3" Then Exit Sub
Dim Arr, i&, col%, d, k
Set d = CreateObject("Scripting.Dictionary")
Arr = [a101].CurrentRegion
For i = 2 To UBound(Arr, 2)
d(Arr(1, i)) = i
Next
col = d(Target.Value)
d.RemoveAll
For i = 2 To UBound(Arr)
If Arr(i, col) = "" Then Exit For
d(Arr(i, col)) = ""
Next
With [A5].Validation
.Delete
.Add 3, 1, 1, Join(d.keys, ",")
End With
k = d.keys
[A5] = k(0)
End Sub
这是一个二级联动的代码
一级下拉菜单在A3单元格
二级下拉菜单在A5单元格
数据区域在一个左上角为a101单元格的矩形区域
想问一下,哪些代码修改后,可以使一级菜单和二级菜单的单元格改到其他地方
If Target.Address <> "$A$3" Then Exit Sub
Dim Arr, i&, col%, d, k
Set d = CreateObject("Scripting.Dictionary")
Arr = [a101].CurrentRegion
For i = 2 To UBound(Arr, 2)
d(Arr(1, i)) = i
Next
col = d(Target.Value)
d.RemoveAll
For i = 2 To UBound(Arr)
If Arr(i, col) = "" Then Exit For
d(Arr(i, col)) = ""
Next
With [A5].Validation
.Delete
.Add 3, 1, 1, Join(d.keys, ",")
End With
k = d.keys
[A5] = k(0)
End Sub
这是一个二级联动的代码
一级下拉菜单在A3单元格
二级下拉菜单在A5单元格
数据区域在一个左上角为a101单元格的矩形区域
想问一下,哪些代码修改后,可以使一级菜单和二级菜单的单元格改到其他地方