楼主
liuguansky |
Q：如何返回不同格式下的数据有效性？
问题1：将一班、二班、三班、四班用函数或VBA取出，并将以有效序列形式写入C12
问题2：将C12值中对应的班级中学生的名字用函数或VBA取出，并以有效序列形式写入D12
问题3：按C12和D12中的值将其对应的成绩用函数或VBA取出，并写入E12
A：用如下代码可以实现：
- Sub validationadd()
- Dim arr, lr&, lc&, i&, j&, str1
- lr = Cells(Rows.Count, 1).End(3).Row
- lc = Cells(lr, 1).End(2).Column
- arr = Cells(1, 1).Resize(lr, lc).Value
- 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)
- With Cells(lr + 2, 3).Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:=str1
- End With
- str1 = ""
- For i = 1 To lr
- If i - 1 Mod 5 > 1 Then
- For j = 1 To lc Step 2
- str1 = str1 & "," & arr(i, j)
- Next j
- End If
- Next i
- str1 = Mid(str1, 2)
- With Cells(lr + 2, 4).Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:=str1
- End With
- str1 = ""
- For i = 1 To lr
- If i - 1 Mod 5 > 1 Then
- For j = 2 To lc Step 2
- str1 = str1 & "," & arr(i, j)
- Next j
- End If
- Next i
- str1 = Mid(str1, 2)
- With Cells(lr + 2, 5).Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:=str1
- End With
- End Sub
支持下右扩展，在数据的下方第二行的第3 4 5 列返回数据有效性
|