[讨论]再问批量连接Email地址用数组方法解决出现的问题

箫风

<p>1月12日兰版已用数组解决，但本人天生爱钻牛角尖，下面是我的解法，但在最后却出错了，苦思瞑想也不能解决。</p><p>Sub 串连邮箱5()<br/>Dim M, N As Long<br/>Dim arr, arr1(), arr2()<br/>arr = Application.Transpose(Range("a2:a" &amp; Range("a65536").End(xlUp).Row))<br/>For N = 1 To UBound(arr)<br/>M = M + 1<br/>ReDim Preserve arr1(1 To M)<br/>arr1(M) = Application.Index(arr, N)<br/>If M = 20 Or N = UBound(arr) Then<br/>x = x + 1<br/>ReDim Preserve arr2(1 To x)<br/>arr2(x) = Join(arr1, ",")<br/>Erase arr1<br/>M = 0<br/>GoTo 100<br/>End If<br/>100:<br/>Next N<br/>Range("c2").Resize(x, 1) = Application.Transpose(arr2)&nbsp; '就是这句出错，说什么类型不匹配。<br/>End Sub</p>

[此贴子已经被作者于2008-1-15 11:22:42编辑过]

hzhxw5570

呵呵.我不会

兰色幻想

代码没有错,是transpose转置的对象好象有字符个数限制,你把A列改字一个字母试试.

箫风

<p>兰版说的没错，我试过了转置函数最多只能有255个字符，多了就会报错。我只能这样改了：</p><p>Sub 串连邮箱5()<br/>Dim M, N As Long<br/>Dim arr, arr1(), arr2()<br/>t = Timer<br/>arr = Application.Transpose(Range("a2:a" &amp; Range("a65536").End(xlUp).Row))<br/>For N = 1 To UBound(arr)<br/>M = M + 1<br/>ReDim Preserve arr1(1 To M)<br/>arr1(M) = Application.Index(arr, N)<br/>If M = 20 Or N = UBound(arr) Then<br/>x = x + 1<br/>ReDim Preserve arr2(1 To x)<br/>arr2(x) = Join(arr1, ",")<br/>Erase arr1<br/>M = 0<br/>GoTo 100<br/>End If<br/>100:<br/>Next N<br/>Range("d2").Resize(1, x) = arr2<br/>Range("d2").Resize(1, x).Copy<br/>Range("C2").PasteSpecial Paste:=xlPasteValues, Transpose:=True<br/>Range("d2").Resize(1, x) = ""<br/>Application.CutCopyMode = False<br/>Range("c2").Select<br/>MsgBox Timer - t<br/>End Sub</p><p>原来还想让速度更快一些，这样还是比兰版的慢了许多。</p>

[此贴子已经被作者于2008-1-15 16:23:08编辑过]