|
- Sub test()
- Dim r%, i%
- Dim arr, brr
- Dim ws As Worksheet
- Dim wb As Workbook
- Dim mypath$, myname$
- Dim reg As New RegExp
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("查询对象")
- r = .Cells(.Rows.Count, 2).End(xlUp).Row
- arr = .Range("b2:b" & r)
- For i = 1 To UBound(arr)
- d(arr(i, 1)) = ""
- Next
- End With
- With reg
- .Global = True
- .Pattern = Join(d.keys, "|")
- End With
- mypath = ThisWorkbook.Path & ""
- myname = Dir(mypath & "*.xlsx")
- Do While myname <> ""
- If myname <> ThisWorkbook.Name Then
- Set wb = GetObject(mypath & myname)
- With wb
- For Each ws In .Worksheets
- With ws
- r = .Cells(.Rows.Count, 2).End(xlUp).Row
- If r = 1 Then
- Exit For
- End If
- brr = .Range("b1:b" & r)
- For i = 2 To UBound(brr)
- Set mh = reg.Execute(brr(i, 1))
- For j = 0 To mh.Count - 1
- xm = mh(j)
- If d.exists(xm) Then
- If Not IsArray(d(xm)) Then
- m = 1
- ReDim crr(1 To m)
- Else
- crr = d(xm)
- m = UBound(crr) + 1
- ReDim Preserve crr(1 To m)
- End If
- crr(m) = ws.Name & "!" & .Cells(i, 2).Address
- d(xm) = crr
- End If
- Next
- Next
- End With
- Next
- .Close False
- End With
- End If
- myname = Dir()
- Loop
- With Worksheets("查询对象")
- r = .Cells(.Rows.Count, 2).End(xlUp).Row
- For i = 2 To r
- xm = .Cells(i, 2).Value
- If IsArray(d(xm)) Then
- crr = d(xm)
- .Cells(i, 3).Resize(1, UBound(crr)) = crr
- End If
- Next
- End With
- End Sub
复制代码 |
|