作者ptguitar (佩蒂吉他)
看板Office
标题[算表] 阵列移除重复写入工作表内vba?
时间Sat Apr 1 09:35:25 2017
软体:excel
版本:2007以上
要将某阵列移除重复并且写入工作表内
我找了个巨集
Sub EX()
Dim AR, D As Object, E As Variant
Dim i As Integer
AR = Array("XX", "aa", "ab", "ab", "ab", "XX", "ab", "ab", "XX", "YY")
Set D = CreateObject("SCRIPTING.DICTIONARY") '字典物件
For Each E In AR
For i = 1 To 10
D(E) = ""
Range("A" & i) = D.KEYs
i = i + 1
Next i
Next
AR = D.KEYs
MsgBox Join(AR, vbLf)
End Sub
但是写入储存格都只有留下XX...
不知道是哪边逻辑思考错误了
另外要怎麽自动判断i要设定多少(就是阵列有多少个)
感谢各位大大指点
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 223.137.217.25
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Office/M.1491010527.A.22D.html
※ 编辑: ptguitar (223.137.217.25), 04/01/2017 09:37:05
1F:→ soyoso: 如要判断阵列中元素总数的话可以ubound 04/01 10:34
2F:→ soyoso: 巨集for next counter为变数i,而回圈内变数i累加,这样进 04/01 10:39
3F:→ soyoso: 入该回圈时会以1,3,5,7,9的值带入到range("a"&i)内 04/01 10:40
4F:→ soyoso: 如要带入唯一值,可於for each..next内只留d(e)="" 04/01 10:42
5F:→ soyoso: 於执行回圈後以d.count来取得d的元素总数 04/01 10:44
6F:→ soyoso: 以range.resize(d.count)=worksheetfunction.transpose 04/01 10:45
7F:→ soyoso: (d.keys)方式写入到储存格内 04/01 10:46
8F:→ ptguitar: 感谢S大 有点复杂来研究看看 04/03 11:41