Board logo

標題: [發問] 請問如何將這句+{sum(if......,if......,))}, 用在Macro裡面?? [打印本頁]
作者: idsmchow    時間: 2011-12-24 00:29     標題: 請問如何將這句+{sum(if......,if......,))}, 用在Macro裡面??

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

請問如何將這句+{sum(if......,if......,))}, 用在Macro裡面??
作者: register313    時間: 2011-12-24 11:27

回復 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
複製代碼
[attach]8917[/attach]
作者: GBKEE    時間: 2011-12-24 17:43

本帖最後由 GBKEE 於 2011-12-24 17:46 編輯

回復 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    時間: 2011-12-25 10:28

本帖最後由 Hsieh 於 2011-12-25 16:13 編輯

回復 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
複製代碼
[attach]8922[/attach]
作者: idsmchow    時間: 2011-12-26 00:44

回復 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    時間: 2011-12-26 17:13

回復 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
複製代碼
[attach]8924[/attach]
作者: idsmchow    時間: 2011-12-27 00:25

回復 6# register313


    Thank

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

謝謝耐心教導
作者: register313    時間: 2011-12-27 08:30

回復 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    時間: 2011-12-27 23:52

Thank
明白了



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)