[發問] 請問如何將這句+{sum(if......,if......,))}, 用在Macro裡面??

idsmchow

一個Workbook裡面有兩將Sheets, 分別名為"Report" & "Data".
當三個條件Match 的情況下, 就將Amount 加起來.

請問如何將這句+{sum(if......,if......,))}, 用在Macro裡面??

register313

回復 1# idsmchow
初學者VBA

1. Sub xx()
   
2. Set Rng1 = Sheets("Data").Columns("A:C")
   
3. Set Rng2 = Sheets("Report").Range("A1:B2")
   
4. C = 10
   
5. Do Until Sheets("Report").Cells(C, 2) = ""
   
6.    If Sheets("Report").Cells(C, 1) = "" Then
   
7.       Sheets("Report").Range(Cells(C, 5), Cells(C, 6)) = 0
   
8.    End If
   
9.    If Sheets("Report").Cells(C, 1) = "Y" Then
   
10.       Rng2.Cells(1, 1) = "Code"
    
11.       Rng2.Cells(1, 2) = "Type"
    
12.       Rng2.Cells(2, 1) = Sheets("Report").Cells(C, 2)
    
13.       Rng2.Cells(2, 2) = "A"
    
14.       Sheets("Report").Cells(C, 5) = WorksheetFunction.DSum(Rng1, 3, Rng2)
    
15.       Rng2.Cells(2, 2) = "B"
    
16.       Sheets("Report").Cells(C, 6) = WorksheetFunction.DSum(Rng1, 3, Rng2)
    
17.    End If
    
18.    C = C + 1
    
19. Loop
    
20. Rng2.Clear
    
21. End Sub

複製代碼

formular.rar (9.23 KB)

下載次數: 8

2011-12-24 11:27

GBKEE

回復 1# idsmchow
自動篩選

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim C As Integer
   
4.     C = 10
   
5.     With Sheets("Report")
   
6.         Do Until .Cells(C, "B") = ""
   
7.             If .Cells(C, "A") = "" Then
   
8.                 .Range(.Cells(C, "E"), .Cells(C, "G")) = 0
   
9.             ElseIf .Cells(C, "A") = "Y" Then
   
10.                 Sheets("Data").Cells.AutoFilter
    
11.                 Sheets("Data").Cells.AutoFilter 1, "A"
    
12.                 Sheets("Data").Cells.AutoFilter 2, .Cells(C, "B")
    
13.                 .Cells(C, "E") = Application.Sum(Sheets("Data").Columns(3).SpecialCells(xlCellTypeVisible))
    
14.                 Sheets("Data").Cells.AutoFilter
    
15.                 Sheets("Data").Cells.AutoFilter 1, "B"
    
16.                 Sheets("Data").Cells.AutoFilter 2, .Cells(C, "B")
    
17.                 .Cells(C, "F") = Application.Sum(Sheets("Data").Columns(3).SpecialCells(xlCellTypeVisible))
    
18.                 .Cells(C, "G") = .Cells(C, "E") + .Cells(C, "F")
    
19.             End If
    
20.             C = C + 1
    
21.         Loop
    
22.     End With
    
23.     Sheets("Data").AutoFilterMode = False
    
24. End Sub

複製代碼

Hsieh

回復 1# idsmchow

這個問題在2007版本以後就很簡單用SUMIFS函數錄製巨集即可
2003版就寫個多條件加總函數來運用
一般模組

1. Function SumIIF(SumRange As Range, ParamArray OtherArgs())'多條件加總函數，可直接運用到儲存格
   
2. '語法:SumIIF(SumRange,準則1,準則範圍1,準則2,準則範圍2,準則3,準則範圍3...準則n,準則範圍n)
   
3. '準則範圍大小必須與SumRange範圍大小相同
   
4. Dim mystr$, temp$
   
5. Set dic = CreateObject("Scripting.Dictionary")
   
6. For i = LBound(OtherArgs) To UBound(OtherArgs) Step 2
   
7.   If mystr = "" Then mystr = OtherArgs(i) Else mystr = mystr & OtherArgs(i)
   
8.   dic(i + 1) = ""
   
9. Next
   
10. For i = 1 To SumRange.Count
    
11.     For Each ky In dic.keys
    
12.       If temp = "" Then temp = OtherArgs(ky)(i) Else temp = temp & OtherArgs(ky)(i)
    
13.     Next
    
14.     If temp = mystr Then SumIIF = SumIIF + SumRange(i)
    
15.     temp = ""
    
16. Next
    
17. End Function
    
18. Sub ex()'主程式
    
19. Dim Sr As Range
    
20. With Sheets("Data")
    
21. Set Sr = .Range("C2", .[C2].End(xlDown))
    
22. Set cr1 = .Range("A2").Resize(Sr.Count, 1)
    
23. Set cr2 = .Range("B2").Resize(Sr.Count, 1)
    
24. End With
    
25. With Sheets("Report")
    
26. For Each c In .[E8:F8]
    
27. For Each a In .Range("A:A").SpecialCells(xlCellTypeConstants)
    
28.    If a = "Y" Then .Cells(a.Row, c.Column) = SumIIF(Sr, c, cr1, a.Offset(, 1), cr2)
    
29. Next
    
30. Next
    
31. End With
    
32. End Sub

複製代碼

formular.rar (11.79 KB)

下載次數: 12

2011-12-25 16:13

idsmchow

回復 3# GBKEE


謝謝(register313, GBKEE, Hsieh)各位回覆.

另外還有一個問題

.Cells(C, "G") = .Cells(C, "E") + .Cells(C, "F")  ‘這句語法能夠將Column 的Total 加起來.

至於 Row 的Total 又如何做法? (Sub Total & Grand Total)

因為A/C Code 有可能增加 / 減少, 資料範圍隨時會變動.

例如:

A/C CODE

101-1
101-2
101-3
101-4

Sub-Total

215-2018
215-2019

Sub-Total

215-3022
215-3023
215-3024
215-3025
215-3026

Sub-Total

Grand Total

register313

回復 5# idsmchow
初學者VBA

1. Sub aa()
   
2. LastCol = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column
   
3. LastRow = ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
   
4. For C = 2 To LastCol - 1
   
5.   X = 1
   
6.   For R = 2 To LastRow
   
7.       If Cells(R, 1) = "Sub Total" Then
   
8.          Cells(R, C) = Application.Sum(Cells(X + 1, C).Resize(R - X - 1, 1))
   
9.          X = R
   
10.       End If
    
11.       Cells(R, LastCol) = Application.Sum(Cells(R, 2).Resize(1, LastCol - 2))
    
12.   Next R
    
13.   Cells(LastRow, C) = Application.Sum(Cells(2, C).Resize(LastRow - 2, 1)) / 2
    
14. Next C
    
15. End Sub

複製代碼

budget.rar (7.68 KB)

下載次數: 12

2011-12-26 17:13

idsmchow

回復 6# register313


    Thank

可否解釋  LastCol - 1,  Cells(R, C) = Application.Sum(Cells(X + 1, C).Resize(R - X - 1, 1)) X = R, 這兩句?

謝謝耐心教導

register313

回復 7# idsmchow

Cells(X + 1, C).Resize(R - X - 1, 1)
'以前面之單元儲存格為基準,擴大儲存格範圍之列數與行數
'以Cells(X + 1, C)單元儲存格為基準,擴大儲存格範圍為R - X - 1列與1行

Application.Sum (Cells(X + 1, C).Resize(R - X - 1, 1))
'儲存格範圍作加總

idsmchow

Thank
明白了