各位先進前輩
次數統計問題己解決,但數量加總懇請協助指導。
例如:
[E35]之值為單號=ABS and 地區=WZ and 品名=K 符合上述三條件之銷售數量加總。
請看附件B1
下列程式如何修改成可以加總
For i = 5 To 11
For m = 35 To 42
.Cells(m, i) = f(.Cells(10, i) & .Cells(m, 3) & .Cells(m, 4))
Next m
Next i
感謝
[attach]402[/attach]作者: b9208 時間: 2010-5-15 14:14
各位先進前輩
上網查詢到使用 Application.Evaluate("sumproduct()")
程式碼如下:但執行後儲存格內為〞#NAME?〞
懇請前輩們指導。
m = Sheets("Re").Range("C65536").End(xlUp).Row
For i = 5 To 11
For j = 35 To 42
Cells(j, i) = Application.Evaluate("sumproduct((MID(Re!C7:C100, 5, 3) = cells(34, i))*(Re!K7:K100 = cells(j, 3))*(Re!AM7:AS100 = cells(j, 4))*(Re!BA7:BG100))")
Next j
Next i作者: b9208 時間: 2010-5-15 14:16
For i = 5 To 11
For j = 35 To 42
Cells(j, i).FormulaR1C1 = "=SUMPRODUCT((MID(Re!R7C3:R100C3, 5, 3) =R34C)*(Re!R7C11:R100C11 =RC3)*(Re!R7C39:R100C45 =RC4)*(Re!R7C53:R100C59))"
Cells(j, i) = Cells(j, i).Value
Next
Next
Set Rng = .Cells(A.row, "AM").Resize(, 7)
If Application.CountA(Rng) > 0 Then Set Rng = Rng.SpecialCells(xlCellTypeConstants) Else GoTo 10
For Each B In Rng
上列程式中,如 Reg 中只有 AM, AP, AR, AS等四欄位有資料(AN. AO, AQ三欄空白),
請問 For Each B In Rng 會執行幾迴圈?