[已解决]求用vba解决问题

sqeel

问题在里面，谢谢

最佳答案

月排行榜 / 总排行榜

心正意诚身修

2016-1-24 22:30

1. Sub test()
   
2. Dim a, b(), s%, k%, d
   
3. Set d = CreateObject("scripting.dictionary")
   
4. For s = 1 To 4
   
5.      a = Sheets(s).Range("a1").CurrentRegion
   
6.     For k = 3 To UBound(a)
   
7.         d(a(k, 1)) = a(k, UBound(a, 2))
   
8.     Next
   
9. Next
   
10. s = [a65536].End(3).Row
    
11. a = Range("a3:a" & s)
    
12. ReDim b(1 To s, 1 To 1)
    
13. For k = 1 To s - 2
    
14.     b(k, 1) = d(a(k, 1))
    
15. Next
    
16. Range("b3:b9999").Clear
    
17. [b3].Resize(s) = b
    
18. MsgBox "刷新完毕"
    
19. End Sub
    

复制代码

跳转到最佳答案楼层

心正意诚身修

1. Sub test()
   
2. Dim a, b(), s%, k%, d
   
3. Set d = CreateObject("scripting.dictionary")
   
4. For s = 1 To 4
   
5.      a = Sheets(s).Range("a1").CurrentRegion
   
6.     For k = 3 To UBound(a)
   
7.         d(a(k, 1)) = a(k, UBound(a, 2))
   
8.     Next
   
9. Next
   
10. s = [a65536].End(3).Row
    
11. a = Range("a3:a" & s)
    
12. ReDim b(1 To s, 1 To 1)
    
13. For k = 1 To s - 2
    
14.     b(k, 1) = d(a(k, 1))
    
15. Next
    
16. Range("b3:b9999").Clear
    
17. [b3].Resize(s) = b
    
18. MsgBox "刷新完毕"
    
19. End Sub
    

复制代码

心正意诚身修

附件

sqeel

心正意诚身修 发表于 2016-1-24 22:30
附件

看不懂能不能给讲解一下每一句的含义

心正意诚身修

1. Sub test()
   
2. Dim a, b(), s%, k%, d
   
3. Set d = CreateObject("scripting.dictionary")
   
4. For s = 1 To 4  '循环前面四个工作簿
   
5.      a = Sheets(s).Range("a1").CurrentRegion  '赋值单元格数据给数组a
   
6.     For k = 3 To UBound(a)  '循环数组
   
7.         d(a(k, 1)) = a(k, UBound(a, 2))  '放进字典
   
8.     Next
   
9. Next
   
10. s = [a65536].End(3).Row  '判断汇总表的最后一行
    
11. a = Range("a3:a" & s) '重新定义数组a
    
12. ReDim b(1 To s, 1 To 1)  '重新定义数组b的大小
    
13. For k = 1 To s - 2 '循环数组
    
14.     b(k, 1) = d(a(k, 1))  '赋值字典的item
    
15. Next
    
16. Range("b3:b9999").Clear '删除之前的数据
    
17. [b3].Resize(s) = b '把单元格的数据赋值给单元格
    
18. MsgBox "刷新完毕"
    
19. End Sub
    

复制代码

心正意诚身修

'循环前面四个工作表

sqeel

心正意诚身修 发表于 2016-1-24 22:48
'循环前面四个工作表

怎样套进实际的表格，用不了

sqeel

我觉得vba里套用vlookup来找合计栏要通用