|
本帖最后由 hktk_hrl 于 2011-12-5 13:37 编辑
需求:有一个表格如下:
Name | Number | A1 | p81348318 | A2 | w55138413 | B1 | w55553413 | D1 | m55384381 | C3 | m88438131 | 现在想要统计同一Number下的Name,并按照下表的格式给出结果
Number | Name | p81348318 | A1 | w55138413 | A2,B1 | 也就是将Number相同的Name项汇总到一个单元格内,并用中文逗号隔开。
我自己写了段代码如下:
- Sub PreSort()
- '
- Dim rng As Range
- Dim lastR As Integer
-
- Set rng = Sheet1.UsedRange.End(xlDown)
- lastR = rng.Row
-
- Number = Sheet1.Range("B2:B" & lastR)
- Name = Sheet1.Range("A2:A" & lastR)
-
- Dim d
- Set d = CreateObject("Scripting.Dictionary")
- For i = 1 To UBound(Number)
- If d.exists(Number(i, 1)) Then
- d(Number(i, 1)) = d(Number(i, 1)) & "," & Name(i, 1)
- Else
- d(Number(i, 1)) = Name(i, 1)
- End If
- Next
-
- Sheet2.[A1].Resize(1, 2) = Array("Number", "Name")
- Sheet2.[A2].Resize(d.Count, 1) = Application.Transpose(d.keys)
- Sheet2.[B2].Resize(d.Count, 1) = Application.Transpose(d.items) '运行到这一句总是出错,提示:运行时错误'13',类型不匹配
-
- End Sub
复制代码
每次运行到这一句总是出错
[code=vb]Sheet2.[B2].Resize(d.Count, 1) = Application.Transpose(d.items) '运行到这一句总是出错,提示:运行时错误'13',类型不匹配[/code]
在这一行之前增加断点,然后进入调试,监视d.items的值,应该是正确的,是个一维数组,内容也没错。在论坛里查看其他使用字典统计重复项的例子,将结果写入单元格的部分是一样的,但是我的程序就是一直出错。十分困惑,所以在此求高手指点,感激不尽~
最后附上源文件
Book1.rar
(15.13 KB, 下载次数: 75)
|
|