|
求编一段VB代码,要求如附件,有第一个表按查询,有查询后面单元格输入一些查询的东西,,能在标黄色区域显示查询出的所有相关的东西。能实现吗/ 求老师解答下。
本帖最后由 bb75308973 于 2012-3-11 12:43 编辑
好吧,给你弄了下,你测试看看
在A1 输入要搜索的内容,然后点查询
- Sub 查询()
- Dim a, i As Integer, n As Integer, iadd$, n1 As Integer
- Dim sh As Worksheet, rg As Range, rg1 As Range, rgx As Range, rgy As Range
- Dim sc As Worksheet
- Dim arr
- Set sc = Sheets(1)
- n1 = 2
- a = sc.Range("A1").Value
- Application.ScreenUpdating = False
- On Error Resume Next
- sc.Range("A2:D65536").Clear
- For n = 2 To Sheets.Count
- Set sh = Sheets(n)
- With sh
- Set rg1 = .Cells.Find(a, , , xlPart)
- If Not rg1 Is Nothing Then
- iadd = rg1.Address
- Do
- i = i + 1
- Set rgx = rg1.CurrentRegion
- Set rgy = .Columns(rg1.Column)
- Set rg2 = Application.Intersect(rgx, rgy)
- arr = rg2
- sc.Range("A" & n1).Resize(, 4) = WorksheetFunction.Transpose(arr)
- n1 = n1 + 1
- Set rg1 = .Cells.FindNext(rg1)
- If rg1 Is Nothing Then Exit Do
- Loop Until iadd = rg1.Address
- End If
- End With
- Next
- On Error GoTo 0
- sc.Range("A:D").RemoveDuplicates (Array(1, 2, 3, 4))
- sc.Cells.Font.Size = 10
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|