|
楼主 |
发表于 2020-10-13 15:44
|
显示全部楼层
- Private Sub CommandButton1_Click()
- Application.ScreenUpdating = False
- Dim d As Object, d1 As Object, d2 As Object, d3 As Object, sht As Worksheet, arr, file As String, n%, k%
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- Set d3 = CreateObject("scripting.dictionary")
- Set sht = Worksheets("查询表")
- file = Dir(ThisWorkbook.Path & "\*.xlsx")
- Do While file <> ""
- If file <> ThisWorkbook.Name Then
- Workbooks.Open ThisWorkbook.Path & "" & file
- n = ActiveWorkbook.Worksheets(1).Cells(Rows.Count, 2).End(xlUp).Row
- arr = ActiveWorkbook.Worksheets(1).Range("b2:g" & n)
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 1)) Then
- Set d(arr(i, 1)) = CreateObject("scripting.dictionary")
- Set d1(arr(i, 1)) = CreateObject("scripting.dictionary")
- Set d2(arr(i, 1)) = CreateObject("scripting.dictionary")
- Set d3(arr(i, 1)) = CreateObject("scripting.dictionary")
- End If
- d(arr(i, 1))(arr(i, 2)) = arr(i, 3)
- d1(arr(i, 1))(arr(i, 2)) = arr(i, 4)
- d2(arr(i, 1))(arr(i, 2)) = arr(i, 5)
- d3(arr(i, 1))(arr(i, 2)) = arr(i, 6)
-
- Next i
- ActiveWorkbook.Close False
- End If
- file = Dir
- Loop
- 'v = 2
- 'k = d.Count - 1
- r = sht.[c65536].End(xlUp).Row
- For j = 0 To r - 1
- If d.exists(sht.Cells(j + 2, 2).Value) Then
- If d(sht.Cells(j + 2, 2).Value).Count = 1 Then
- sht.Cells(j + 2, 6) = d(sht.Cells(j + 2, 2).Value).keys
- sht.Cells(j + 2, 7) = d(sht.Cells(j + 2, 2).Value).items
- sht.Cells(j + 2, 8) = d1(sht.Cells(j + 2, 2).Value).items
- sht.Cells(j + 2, 9) = d2(sht.Cells(j + 2, 2).Value).items
- sht.Cells(j + 2, 10) = d3(sht.Cells(j + 2, 2).Value).items
- 'v = v + 1
- Else
- sht.Rows(j + 2 + 1).EntireRow.Resize(d(sht.Cells(j + 2, 2).Value).Count - 1).Insert
- sht.Cells(j + 2, 6).Resize(d(sht.Cells(j + 2, 2).Value).Count, 1) = Application.Transpose(d(sht.Cells(j + 2, 2).Value).keys)
- sht.Cells(j + 2, 7).Resize(d(sht.Cells(j + 2, 2).Value).Count, 1) = Application.Transpose(d(sht.Cells(j + 2, 2).Value).items)
- sht.Cells(j + 2, 8).Resize(d(sht.Cells(j + 2, 2).Value).Count, 1) = Application.Transpose(d1(sht.Cells(j + 2, 2).Value).items)
- sht.Cells(j + 2, 9).Resize(d(sht.Cells(j + 2, 2).Value).Count, 1) = Application.Transpose(d2(sht.Cells(j + 2, 2).Value).items)
- sht.Cells(j + 2, 10).Resize(d(sht.Cells(j + 2, 2).Value).Count, 1) = Application.Transpose(d3(sht.Cells(j + 2, 2).Value).items)
- For Z = 2 To 5
- sht.Cells(j + 2, Z).Resize(d(sht.Cells(j + 2, 2).Value).Count, 1).MergeCells = True
- Next Z
- 'v = v + d(sht.Cells(j + 2, 2).Value).Count
- End If
- End If
- Next
- Application.ScreenUpdating = True
- End Sub
复制代码
劳务数据查询-修改.rar
(30.61 KB, 下载次数: 3)
谢谢老师,根据需要,对原始数据及格式,修改了下,然后把自己想要的结果,根据老师您的代码修改了下,感谢。
|
|