[已解决]如何使用代码补数？

HongMeig

附件 补数附件.rar (131.95 KB, 下载次数: 11)

2013-1-19 22:20 上传

点击文件名下载附件

谢谢！

最佳答案

月排行榜 / 总排行榜

孤单无所谓

2013-1-20 02:41

字典的声明问题

1. Sub test()
   
2. Set d = CreateObject("scripting.dictionary")
   
3. ar = [a1].Resize([a65536].End(3).Row, 4).Value
   
4. For i% = 1 To UBound(ar)
   
5.     d(ar(i, 1) & "|" & ar(i, 2) & "|" & ar(i, 3)) = ar(i, 4)
   
6. Next
   
7. ar = [m1].Resize([m65536].End(3).Row, 3).Value
   
8. For i = 1 To UBound(ar)
   
9.    If d.Exists(ar(i, 1) & "|" & ar(i, 2) & "|" & ar(i, 3)) Then ar(i, 1) = d(ar(i, 1) & "|" & ar(i, 2) & "|" & ar(i, 3))
   
10. Next
    
11. [s1].Resize(i - 1, 1) = ar
    
12. End Sub
    
13. 
    

复制代码

跳转到最佳答案楼层

上清宫主

加辅助列后用函数也很方便
vba:
Sub test()
Dim d As New Dictionary, ar()
ar = [a1].Resize([a65536].End(3).Row, 4).Value
For i% = 1 To UBound(ar)
    d(ar(i, 1) & "|" & ar(i, 2) & "|" & ar(i, 3)) = ar(i, 4)
Next
ar = [m1].Resize([m65536].End(3).Row, 3).Value
For i = 1 To UBound(ar)
   If d.Exists(ar(i, 1) & "|" & ar(i, 2) & "|" & ar(i, 3)) Then ar(i, 1) = d(ar(i, 1) & "|" & ar(i, 2) & "|" & ar(i, 3))
Next
[s1].Resize(i - 1, 1) = ar
End Sub

HongMeig

上清宫主 发表于 2013-1-19 23:07
加辅助列后用函数也很方便
vba:
Sub test()

谢谢老师。代码复进附件去试了一下，会出错。

孤单无所谓

字典的声明问题

1. Sub test()
   
2. Set d = CreateObject("scripting.dictionary")
   
3. ar = [a1].Resize([a65536].End(3).Row, 4).Value
   
4. For i% = 1 To UBound(ar)
   
5.     d(ar(i, 1) & "|" & ar(i, 2) & "|" & ar(i, 3)) = ar(i, 4)
   
6. Next
   
7. ar = [m1].Resize([m65536].End(3).Row, 3).Value
   
8. For i = 1 To UBound(ar)
   
9.    If d.Exists(ar(i, 1) & "|" & ar(i, 2) & "|" & ar(i, 3)) Then ar(i, 1) = d(ar(i, 1) & "|" & ar(i, 2) & "|" & ar(i, 3))
   
10. Next
    
11. [s1].Resize(i - 1, 1) = ar
    
12. End Sub
    
13. 
    

复制代码