|
用vba编写了一个查询页面,通过下拉列表框里的编号查询出“数据源”工作表里的数据,并将它显示出来
代码如下
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$k$2" Then Exit Sub
Dim arr, brr(), d As New Dictionary
With Sheets("数据源")
arr = .Range("g2:q" & .Range("g65536").End(3).Row)
For b = 1 To UBound(arr)
d(arr(b, 1)) = ""
Next
Range("z2").Resize(d.Count) = Application.WorksheetFunction.Transpose(d.Keys)
g = UBound(arr, 2)
Range(Range("a5"), Cells(14, g)) = ""
ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
For x = 1 To UBound(arr)
If arr(x, 1) = Range("k2") Then
k = k + 1
For c = 1 To UBound(arr, 2)
brr(k, c) = arr(x, c)
Next
End If
Next
End With
Range("a5").Resize(k, UBound(arr, 2)) = brr
End Sub
代码似乎没什么问题,但就是找不到数据,求论坛里的高手帮着看看,小弟感激不敬
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Address <> "$K$2" Then Exit Sub
- Application.EnableEvents = False
- Application.ScreenUpdating = False
- Dim arr, brr(), d As New Dictionary
- With Sheets("数据源")
- arr = .Range("a2:q" & .Range("g65536").End(3).Row)
- For b = 1 To UBound(arr)
- d(arr(b, 1)) = ""
- Next
- Range("z2").Resize(d.Count) = Application.WorksheetFunction.Transpose(d.Keys)
- g = UBound(arr, 2)
- Range(Range("a5"), Cells(14, "k")) = ""
- ReDim brr(1 To UBound(arr), 1 To 11)
- For x = 1 To UBound(arr)
- If arr(x, 1) = Range("k2") Then
- k = k + 1
- For c = 1 To UBound(brr, 2)
- brr(k, c) = arr(x, c + 6)
- Next
- End If
- Next
- End With
- If k > 0 Then
- Range("a5").Resize(k, UBound(brr, 2)) = brr
- End If
- Application.EnableEvents = True
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|