|
- Sub hpgld() '合并光缆段
- Dim arr, x As Long, k1, k, t
- arr = Sheets("光缆段").UsedRange
- Set d = CreateObject("Scripting.Dictionary")
- For Each sh In Sheets
- If sh.Name = "光缆段1" Then
- Application.DisplayAlerts = False
- sh.Delete
- Application.DisplayAlerts = True
- End If
- Next
- Sheets.Add.Name = "光缆段1"
- For x = 2 To UBound(arr)
- k1 = arr(x, 1)
- If d.exists(k1) Then
- d(k1) = d(k1) & "<>" & arr(x, 2)
- Else
- d(k1) = arr(x, 2)
- End If
- Next
- k = d.keys
- t = d.Items
- Sheets("光缆段1").Range("A2").Resize(d.Count) = Application.Transpose(k)
- Sheets("光缆段1").Range("b2").Resize(d.Count) = Application.Transpose(t)
- End Sub
复制代码
本帖最后由 france723 于 2017-5-20 23:16 编辑
- Sub hpgld() '合并光缆段
- Dim arr, x As Long, k1, k, t, n,y, m
- arr = Sheets("光缆段").UsedRange
- Set d = CreateObject("Scripting.Dictionary")
- For Each sh In Sheets
- If sh.Name = "光缆段1" Then
- Application.DisplayAlerts = False
- sh.Delete
- Application.DisplayAlerts = True
- End If
- Next
- Sheets.Add.Name = "光缆段1"
- For x = 2 To UBound(arr)
- k1 = arr(x, 1)
- If d.exists(k1) Then
- d(k1) = d(k1) & "<>" & arr(x, 2)
- Else
- d(k1) = arr(x, 2)
- End If
- Next
- k = d.keys
- Sheets("光缆段1").Range("A2").Resize(d.Count) = Application.Transpose(k)
- y = Sheets("光缆段1").Range("a65536").End(3).Row
- For n = 2 To y
- m = Sheets("光缆段1").Range("A" & n)
- Sheets("光缆段1").Range("B" & n) = d(m)
- Next n
- End Sub
复制代码字典里key和Item相互对应,不能像你那样直接派出所有ITEM,和你KEY对不上
|
|