Application.transpose()行列转置函数,对转置时每个单元格内可容纳的字符长度有限制要求。
即每个单元格内的字符数不得大于255个字符。
如果有单元格内的字符数大于255了,会提示运行时错误13,类型不匹配。
遇到这种情况,可以用一维单行数组转二维单列数组的方法做变通。类似下面的例子。
- Sub PreSort3()
- arr = Sheet1.[a1].CurrentRegion
-
- Set d = CreateObject("Scripting.Dictionary")
- For i = 2 To UBound(arr)
- If d.Exists(arr(i, 2)) Then
- d(arr(i, 2)) = d(arr(i, 2)) & "," & arr(i, 1)
- Else
- d(arr(i, 2)) = arr(i, 1)
- End If
- Next
-
- p = d.keys
- q = d.items
-
- ReDim brr(d.Count, 1)
- brr(0, 0) = "Number"
- brr(0, 1) = "Name"
-
- For i = 1 To d.Count
- brr(i, 0) = p(i - 1)
- brr(i, 1) = q(i - 1)
- Next
-
- Sheet2.[a1].CurrentRegion = ""
- Sheet2.[a1].Resize(d.Count + 1, 2) = brr
-
- End Sub
复制代码 |