kimbal

¦^´_ 1# yliu

1. Private Sub CommandButton2_Click()
   
2. Application.ScreenUpdating = False
   
3. Application.Calculation = xlCalculationManual
   
4. 
   
5. Dim rowcnt As Long
   
6. Dim i As Long
   
7. 
   
8. Dim lngVlookupRow As Long
   
9. Dim lngCurrenMonth As Long
   
10. Dim lngCurrValue As Long
    
11. 
    
12. '²M²z¼ƾÚ
    
13. Sheets("Á`ªí").Activate
    
14. Sheets("Á`ªí").Range("A3").CurrentRegion.Offset(1, 1).Clear
    
15. 
    
16. 
    
17. Sheets("­ì©l¸ê®Æ").Activate
    
18. With Sheets("­ì©l¸ê®Æ")
    
19.     rowcnt = .Cells(1, 1).CurrentRegion.Rows.Count
    
20.     For i = 1 To rowcnt
    
21.         lngCurrenMonth = 0
    
22.         If IsDate(.Cells(i, 2)) Then
    
23.             lngCurrenMonth = Month(.Cells(i, 2)) '·í¦æ¤ë¥÷
    
24.         End If
    
25.         If lngCurrenMonth > 0 And Not (IsError(Application.Match(.Cells(i, 1), Sheets("Á`ªí").Range("A:A"), 0))) Then
    
26.             lngVlookupRow = Application.Match(.Cells(i, 1), Sheets("Á`ªí").Range("A:A"), 0) '·í¦æÃþ«¬¦bÁ`ªí¦æ¼Æ
    
27.             lngCurrValue = .Cells(i, 3)
    
28.             With Sheets("Á`ªí").Cells(lngVlookupRow, lngCurrenMonth + 1) '©ñ¼Æ¾Ú¨ìÁ`ªí¤W
    
29.                 .Value = .Value + lngCurrValue
    
30.             End With
    
31.         End If
    
32.     Next
    
33.    
    
34. End With
    
35. 
    
36. Application.Calculation = xlCalculationAutomatic
    
37. Application.ScreenUpdating = True
    
38. End Sub

½Æ»s¥N½X

kimbal

¦^´_ 3# yliu


    ¥i§_´£¨Ñ¤@¤UEXCEL?

¥t¥~¤]·|«Øij¥ÎPIVOTTABLE ©MSUMIFS
VBA LOOP«Ü¦h±¡ªp¤]¬O¤ñ¤½¦¡ºCªº.