[已解决]dsmch老师，还有一些疑问？

新一

总表，黄色底纹，为何不能更新在表一和表二中？

最佳答案

月排行榜 / 总排行榜

dsmch

2014-4-25 19:46

1. Sub Macro3()
   
2. Dim arr, brr, crr, d, i&, j%, k%, zf$
   
3. Set d = CreateObject("scripting.dictionary")
   
4. arr = Range("a2").CurrentRegion
   
5. For i = 3 To UBound(arr)
   
6.     zf = arr(i, 8) & "," & arr(i, 9) & "," & arr(i, 1)
   
7.     d(zf) = d(zf) + arr(i, 7)
   
8. Next
   
9. For k = 1 To 2
   
10.     brr = Sheets(k).Range("a2:ah" & Sheets(k).Range("a65536").End(xlUp).Row)
    
11.     For i = 3 To UBound(brr)
    
12.         For j = 4 To UBound(brr, 2)
    
13.             zf = brr(i, 2) & "," & brr(i, 3) & "," & brr(1, j)
    
14.             brr(i, j) = d(zf)
    
15.         Next
    
16.     Next
    
17.     Sheets(k).Range("a2").Resize(UBound(brr), UBound(brr, 2)) = brr
    
18. Next
    
19. End Sub
    

复制代码

跳转到最佳答案楼层

su45

帮顶下，咱等好学下！

新一

表一，表二，两个日期合计已用黄色底纹标示，在总表中，表一，表二刷新按钮后，合计不对？

xdragon

1. Sub Macro6()
   
2.    cx "表二"
   
3.    cx "表一"
   
4. End Sub
   
5. 
   
6. Sub cx(ShtName As String)
   
7.     Dim arr, brr, crr, d, i&, j%, k%, zf$
   
8.     Set d = CreateObject("scripting.dictionary")
   
9.     arr = Range("a2").CurrentRegion
   
10.     For i = 3 To UBound(arr)
    
11.         zf = arr(i, 8) & "," & arr(i, 9) & "," & arr(i, 1)
    
12.         d(zf) = arr(i, 7)
    
13.     Next
    
14.     brr = Sheets(ShtName).Range("a2:ah" & Sheets(ShtName).Range("a65536").End(xlUp).Row)
    
15.     For i = 3 To UBound(brr)
    
16.         For j = 4 To UBound(brr, 2)
    
17.             zf = brr(i, 2) & "," & brr(i, 3) & "," & brr(1, j)
    
18.             brr(i, j) = d(zf)
    
19.         Next
    
20.     Next
    
21.     Sheets(ShtName).Range("a2").Resize(UBound(brr), UBound(brr, 2)) = brr
    
22. End Sub

复制代码

把你的macro6稍微改动了一下就好了，原因是你的sheets默认index变动过了，用工作表名字不容易出错（当然，你要把工作表名改了就没辙了）

dsmch

1. Sub Macro3()
   
2. Dim arr, brr, crr, d, i&, j%, k%, zf$
   
3. Set d = CreateObject("scripting.dictionary")
   
4. arr = Range("a2").CurrentRegion
   
5. For i = 3 To UBound(arr)
   
6.     zf = arr(i, 8) & "," & arr(i, 9) & "," & arr(i, 1)
   
7.     d(zf) = d(zf) + arr(i, 7)
   
8. Next
   
9. For k = 1 To 2
   
10.     brr = Sheets(k).Range("a2:ah" & Sheets(k).Range("a65536").End(xlUp).Row)
    
11.     For i = 3 To UBound(brr)
    
12.         For j = 4 To UBound(brr, 2)
    
13.             zf = brr(i, 2) & "," & brr(i, 3) & "," & brr(1, j)
    
14.             brr(i, j) = d(zf)
    
15.         Next
    
16.     Next
    
17.     Sheets(k).Range("a2").Resize(UBound(brr), UBound(brr, 2)) = brr
    
18. Next
    
19. End Sub
    

复制代码