|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
谢谢论坛热心的大神,本人新手,自己写了一段代码,但有一点小问题,一直没解决,第一位帮助我解决的网友留下支付宝账号,我会发一个小红包,再次感谢!
实例如下:
附件中,从一个文件夹下所有工作表中,查找包含“要查找目录.xls” A列的内容,并把查找到的结果整行复制到“要查找目录.xls” 的sheet2中。
我的问题:
1、查找的结果中有很多的空行,不知道原因;
2、数据很多很大的时候是否需要把要查找的内容写入数组提高效率;
3、麻烦老师帮我指出问题所在并调试一下,拜谢!!
代码和附件如下:
- Sub test()
- Dim mypath$, myname$
- mypath = ThisWorkbook.Path & ""
- myname = Dir(mypath & "*.*")
- Do While myname <> "" And myname <> "要查找的目录.xls"
- With Workbooks.Open(mypath & myname)
- .Sheets(1).Select
- Call 查找
- .Close True
- End With
- myname = Dir
- Loop
- End Sub
- Sub 查找()
- Dim dic
- Dim m, irow As Integer
- Set dic = CreateObject("scripting.dictionary")
- brr = ActiveWorkbook.Sheets(1).Range("a1").CurrentRegion.Value
- ReDim arr(1 To UBound(brr), 1 To UBound(brr, 2))
- irow = ThisWorkbook.Sheets(1).Range("a65536").End(xlUp).Row
- For m = 1 To irow
- With ActiveWorkbook.Sheets(1).Range("1:65536")
- Set c = .Find(ThisWorkbook.Sheets(1).Range("a" & m).Value, LookIn:=xlValues, lookat:=xlPart)
- If Not c Is Nothing Then
- firstaddress = c.Address
- Do
- If Not dic.exists(c.Row) Then
- dic(c.Row) = dic.Count + 1
- n = dic.Count
- For j = 1 To UBound(brr, 2)
- arr(n, j) = brr(c.Row, j)
- Next
- End If
- Set c = .FindNext(c)
- Loop While Not c Is Nothing And c.Address <> firstaddress
- End If
- End With
- If c Is Nothing Then
- k = 2
- Else:
- ThisWorkbook.Sheets(2).Range("a" & Range("a65536").End(xlUp).Row).Resize(dic.Count, UBound(brr, 2)).Value = arr
- End If
- Next
- End Sub
复制代码
|
|