楼主
liuguansky |
Q：如何返回不同格式下的多个二级数据有效性？
1.如果C12=“一班”，那些D12只显示出属于一班的所有名字，其它班级的姓名刚不显示
2.如果C12="一班",D12=“李一”，那E12就自动查找属于“一班”、“李一”的成绩，其它成绩将不显示，也就是说E12不是序 列形式
A；用如下代码可以实现：
添加模块validationadd
- Public lr&
- Sub validationadd()
- Dim arr, lc&, i&, j&, arrre, str1$, n%, m&, arr1, str2$, str3$, arr2
- Dim rng As Range
- lr = Cells(Rows.Count, 1).End(3).Row
- lc = Cells(lr, 1).End(2).Column
- arr = Cells(1, 1).Resize(lr, lc).Value
- ReDim arrre(1 To lr * lc \ 30, 1 To 2)
- For i = 1 To lr Step 5
- For j = 1 To lc Step 6
- str1 = str1 & "," & arr(i, j)
- Next j, i
- str1 = Mid(str1, 2)
- For i = 1 To lr
- If (i - 1) Mod 5 > 1 Then
- For j = 1 To lc
- m = Int((i - 1) / 5) * lc / 6 + Int((j - 1) / 6) + 1
- n = 2 - j Mod 2
- arrre(m, n) = arrre(m, n) & "," & arr(i, j)
- Next j
- End If
- Next i
- For i = 1 To UBound(arrre, 1)
- For j = 1 To UBound(arrre, 2)
- arrre(i, j) = Mid(arrre(i, j), 2)
- Next j, i
- With Cells(lr + 2, 3).Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:=str1
- End With
- arr1 = Split(str1, ",")
- Set rng = Cells(lr + 2, 3)
- str2 = rng.Value
- i = InStr(1, "," & str1, "," & str2)
- If str2 <> "" And i > 0 Then
- j = Application.Match(str2, arr1, 0)
- With rng.Offset(0, 1).Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:=arrre(j, 1)
- End With
- 100
- str3 = rng.Offset(0, 1).Value
- arr2 = Split(arrre(j, 1), ",")
- m = InStr(1, "," & arrre(j, 1), "," & str3)
- If str3 <> "" And m > 0 Then
- n = Application.Match(str3, arr2, 0)
- rng.Offset(0, 2) = Split(arrre(j, 2), ",")(n - 1)
- Else: rng.Offset(0, 1) = Split(arrre(j, 1), ",")(0): GoTo 100
- End If
- End If
- End Sub
sheet1中添加事件
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Row = lr + 2 And Target.Column > 2 And Target.Column < 5 Then
- Application.EnableEvents = False
- validationadd
- Application.EnableEvents = True
- End If
- End Sub
thisworkbook添加事件
- Private Sub Workbook_Open()
- validationadd
- End Sub
|
