GBKEE

回復 10# oiggu

1. Sub Ex()
   
2.     Dim D As Range, R As Range, M$
   
3.     Set D = CreateObject("Scripting.Dictionary")
   
4.     With Sheets("date")
   
5.         For Each R In .Range("b2", .Cells(.[a1].End(xlDown).Row, .[a1].End(xlToRight).Column))
   
6.             M = .Cells(R.Row, 1) & .Cells(1, R.Column)
   
7.             D(M) = D(M) + R
   
8.         Next
   
9.     End With
   
10.     With Sheets("A")
    
11.         For Each R In .Range("b3", .Cells(.[a3].End(xlDown).Row, .[a1].End(xlToRight).Column))
    
12.             M = .Cells(R.Row, 1) & .Cells(1, R.Column)
    
13.             R = D(M)
    
14.         Next
    
15.     End With
    
16. End Sub

複製代碼

Hsieh

回復 10# oiggu
可見你對程式碼並未真正了解含意
況且這種統計用樞紐分析很快就能解決
建議你先把EXCEL基本功能了解
VBA是輔助工具
有了基本操作的基礎,VBA的運用才會靈活

    test.rar (16.58 KB)

下載次數: 4

2010-7-29 19:50

下載 (10.36 KB)

2010-7-29 22:23

1. Sub nn()
   
2. Dim A As Range, B As Range
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. With Sheets("DATE")
   
5.    For Each A In .Range(.[B1], .[B1].End(xlToRight))
   
6.       For Each B In .Range(.[A2], .[A2].End(xlDown))
   
7.          d(A & B) = d(A & B) + Val(.Cells(A.Column, B.Row))
   
8.       Next
   
9.    Next
   
10. End With
    
11. With Sheets("A")
    
12.    For Each A In .Range(.[B1], .[B1].End(xlToRight))
    
13.       For Each B In .Range(.[A3], .[A3].End(xlDown))
    
14.          .Cells(A.Column, B.Row) = d(A & B)
    
15.       Next
    
16.    Next
    
17. End With
    
18. 
    
19. End Sub

複製代碼

zz5151353

個人建議
採用合併彙算跟樞紐分析表
都是快速有效方法