|
楼主 |
发表于 2015-1-1 12:17
|
显示全部楼层
dsmch 发表于 2015-1-1 12:01
………………
Sub 查询()
Dim sht As Worksheet, drow%, i%, j%, Arr, Brr, d, Crr
Set d = CreateObject("scripting.dictionary")
Dim r As Long, c As Long
r = 2
Application.ScreenUpdating = False '屏幕闪烁关闭
Dim filename As String, wb As Workbook, Erow As Long
Dim fn As String
' On Error GoTo VeryEnd
filename = Dir(ThisWorkbook.Path & "\样本\*.xls") '对文件夹内的工作簿进行循环,循环查找的格式 *.xls
' MsgBox filename
Do While filename <> ""
If filename <> ThisWorkbook.Name Then '判断文件是否是本工作簿
' Erow = Range("A1").End(xlDown).Row '取得汇总表中第一条空行行号
' MsgBox "erow=" & Erow
fn = ThisWorkbook.Path & "\样本\" & filename '取得循环符合条件工作簿的 文件夹地址,赋值给fn 这个变量
' MsgBox "现在汇总的工作簿是fn= " & fn
Set wb = GetObject(fn) '将fn代表的工作簿对象赋给变量
' MsgBox wb.Name
Set sht = wb.Worksheets(1) '汇总的是第1张工作表
'将数据表中的记录保存在arr数组里
Arr = sht.Range("a2:e" & sht.Range("a65536").End(3).Row) '将结果存放在定义好的数组arr中
For i = 1 To UBound(Arr)
d(Arr(i, 4)) = Arr(i, 1) & "@" & Arr(i, 2) & "@" & Arr(i, 3) & "@" & Arr(i, 5)
Next
Erase Arr
wb.Close False
End If
filename = Dir '进行下一步的循环
Loop
Dim tt As String
' MsgBox d.Count
With Worksheets("sheet1")
' .Range("l2").Resize(d.Count, 1) = Application.Transpose(d.keys)
' .Range("m2").Resize(d.Count, 1) = Application.Transpose(d.items)
drow = .Range("a65536").End(3).Row
' MsgBox drow
Brr = .Range("a2:e" & drow)
.Range("l17").Resize(UBound(Brr), 5) = Brr
For i = 1 To UBound(Brr)
tt = "@" & CStr(Brr(i, 1))
' MsgBox tt & " " & d(tt)
Crr = Split(d(Brr(i, 1)), "@")
' MsgBox UBound(Crr)
If UBound(Crr) > 0 Then
For j = 0 To 3
Brr(i, j + 2) = Crr(j)
Next
End If
Next
.Range("a2").Resize(UBound(Brr), 5) = Brr
End With
VeryEnd:
Application.ScreenUpdating = True
End Sub
能否把上面代码改成附件中的格式,谢谢
(新)文件.rar
(6.47 KB, 下载次数: 0)
|
|