1.批量查询（批量查询显示表-事件代码）

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

 

Dim d2, d3, vv2, vv3, vv4, vv5 As Long '定义变量 d2主级最右端个数 d3次级下最大行数 vv2循环主级所在列 vv4循环次级所在行 vv5循环并组合序列(QH)

d2 = Sheets("批量查询脚本表").Range("iv3").End(xlToLeft).Column '获取主级栏最右端个数(QH)

 

If Target.Column = 3 And Target.row = 2 Then '如果sheet7.range(c2)有变动(QH)

Sheets("批量查询显示表").Range("c3").Validation.Delete '清空sheet7.range(c3)序列(QH)

Sheets("批量查询显示表").Range("c3").ClearContents '清空sheet7.range(c3)数据(QH)

' Sheets("批量查询显示表").Range("d11:iv100000").ClearContents '清空sheet7查询的结果数据(QH)

For vv2 = 4 To d2 'for循环查找主级所在列(QH)

If Sheets("批量查询脚本表").Cells(3, vv2).Value = Sheets("批量查询显示表").Cells(2, 3).Value Then '如果如果sheet7.range(c2)数值和主级相同(QH)

d3 = Sheets("批量查询脚本表").Cells(60000, vv2).End(xlUp).row '获取主级下次级最大行数(QH)

With Sheets("批量查询脚本表")

For vv3 = 5 To d3 'for循环获取次级序列值(QH)

xulie2 = xulie2 & .Cells(vv3, vv2) & "," '组合次级序列值(QH)

Next

End With

With Sheets("批量查询显示表").Range("c3").Validation

.Delete '再次清空序列(QH)

.Add Type:=xlValidateList, Formula1:=xulie2 '将序列赋值给sheet7.range(c3)(QH)

End With

End If

Next vv2

Else '如果sheet7.range(c2)无变动

If Target.Column = 3 And Target.row = 3 Then '如果sheet7.range(c3)变动

Sheets("批量查询显示表").Range("c4").Validation.Delete '清空sheet7.range(c4)序列(QH)

Sheets("批量查询显示表").Range("c4").ClearContents '清空sheet7.range(c4)数据(QH)

' Sheets("批量查询显示表").Range("d11:iv100000").ClearContents '清空sheet7查询的结果数据(QH)

For vv2 = 4 To d2 'for循环查找主级所在列(QH)

If Sheets("批量查询脚本表").Cells(3, vv2).Value = Sheets("批量查询显示表").Cells(2, 3).Value Then '如果如果sheet7.range(c2)数值和主级相同(QH)

d3 = Sheets("批量查询脚本表").Cells(60000, vv2).End(xlUp).row '获取主级下次级最大行数(QH)

For vv4 = 5 To d3 'for循环获取条件序列值(QH)

If Sheets("批量查询脚本表").Cells(vv4, vv2).Value = Sheets("批量查询显示表").Cells(3, 3).Value Then '如果如果sheet7.range(c3)数值和次级相同(QH)

For vv5 = vv2 + 3 To vv2 + 6

xulie3 = xulie3 & Sheets("批量查询脚本表").Cells(vv4, vv5) & ","

Next vv5

With Sheets("批量查询显示表").Range("c4").Validation

.Delete '再次清空序列(QH)

.Add Type:=xlValidateList, Formula1:=xulie3 '将序列赋值给sheet7.range(c4)(QH)

End With

End If

Next vv4

End If

Next vv2

Else

If Target.Column = 3 And Target.row = 4 Then

Sheets("批量查询显示表").Range("c12:c100000").ClearContents '清空条件数据(QH)

Sheets("批量查询显示表").Range("d11:iv100000").ClearContents

End If

End If

End If

Application.ScreenUpdating = ture

End Sub

Sub xulie1()

Dim d1, vv1 As Long

Dim xulie1 As String

With Sheets("批量查询脚本表")

d1 = .Range("iv2").End(xlToLeft).Column

For vv1 = 4 To d1

xulie1 = xulie1 & .Cells(2, vv1) & ","

Next

End With

With Sheets("批量查询显示表").Range("c2").Validation

.Delete

.Add Type:=xlValidateList, Formula1:=xulie1

End With

End Sub