第8讲示例: VBA数组处理重复值

兰色幻想

<p>大家用半小时的时间做一下,8点半讲解</p><p> 0eLkarhJ.rar (8.99 KB, 下载次数: 169)

2008-1-9 20:04 上传

点击文件名下载附件
第8讲示例: VBA数组处理重复值 <br/></p><p>参考答案:</p><p>Sub 唯一值循环法()<br/>Range("d2:d" &amp; Range("d65536").End(xlUp).Row) = ""<br/>&nbsp;Dim arr1, arr2, arr3()<br/>&nbsp;arr1 = Range("a2:a11")<br/>&nbsp;arr2 = Range("b2:b11")<br/>&nbsp;ReDim arr3(1 To UBound(arr1) + UBound(arr2), 1 To 1)<br/>&nbsp;For x = 1 To UBound(arr1)<br/>&nbsp;&nbsp; arr3(x, 1) = arr1(x, 1)<br/>&nbsp;Next x<br/>&nbsp;'-------------------------<br/>&nbsp;For z = 1 To UBound(arr2)<br/>&nbsp;&nbsp; For y = 1 To UBound(arr1)<br/>&nbsp;&nbsp;&nbsp;&nbsp; If arr2(z, 1) = arr1(y, 1) Then GoTo 100<br/>&nbsp;&nbsp; Next y<br/>&nbsp;&nbsp; k = k + 1<br/>&nbsp;&nbsp; arr3(x + k - 1, 1) = arr2(z, 1)<br/>100:<br/>&nbsp; Next z<br/>&nbsp;Range("d2").Resize(k + x) = arr3<br/>End Sub</p><p>.....................................................................</p><p>Sub test()<br/>Dim d As New Dictionary<br/>&nbsp;Dim arr1, arr2, x As Integer, y As Integer<br/>&nbsp;arr1 = Range("a2:a11")<br/>&nbsp;arr2 = Range("b2:b11")<br/>&nbsp;<br/>&nbsp;For x = 1 To UBound(arr1)<br/>&nbsp; d(arr1(x, 1)) = x<br/>&nbsp;Next x<br/>&nbsp;<br/>&nbsp;For y = 1 To UBound(arr2)<br/>&nbsp; d(arr2(y, 1)) = y<br/>&nbsp;Next y<br/>&nbsp;<br/>&nbsp;If d.Exists("A8") Then<br/>&nbsp;&nbsp; MsgBox "存在"<br/>&nbsp; Else<br/>&nbsp;&nbsp; MsgBox "不存在"<br/>&nbsp;End If<br/>&nbsp;Range("d2").Resize(d.Count) = Application.Transpose(d.Items)<br/>End Sub</p>

[此贴子已经被作者于2008-1-9 22:38:45编辑过]

hong4777

下载了.

yuhai0012

<p>谢谢兰版</p>

zgwei050

<p>[em01]</p>

为工作

<p>来了,谢谢兰版........</p>

hhzjxss

来了，做做看！

晓

下载了

zjxia889

<p>Sub wyz()<br/>Range("D2:D" &amp; Range("D65536").End(xlUp).Row).ClearContents<br/>Dim arr1(1 To 10, 1 To 1), arr2(1 To 10, 1 To 1), arr3()<br/>arr1 = Range("A2:A11")<br/>arr2 = Range("B2:B11")<br/>ReDim arr3(1 To UBound(arr1) + UBound(arr2), 1 To 1)<br/>arr3 = arr1<br/>x = UBound(arr3)<br/>For k = 1 To UBound(arr2)<br/>&nbsp;For l = 1 To UBound(arr1)<br/>&nbsp; If arr1(l, 1) = arr2(k, 1) Then GoTo 100<br/>&nbsp;Next l<br/>x = x + 1<br/>arr3(x + l - 1, 1) = arr2(k, 1)<br/>100:</p><p>Next k<br/>Range("e2").Resize(UBound(arr3)) = arr3<br/>End Sub<br/></p>

[此贴子已经被作者于2008-1-9 21:41:11编辑过]

刺桐

<p>已下载</p>

lpz001

来啦！