麻辣家族討論版版 - Powered by Discuz! Board

標題: sumifs 轉成 vba 語法 [打印本頁]

作者: jsc0518 時間: 2017-5-30 18:29 標題: sumifs 轉成 vba 語法

Dear 各位先進，
我有寫了函數
=SUMIFS(R5:R50,O5:O50,A5,P5:P50,B5,Q5:Q50,C5)

有找了網路寫成VBA的作業，但仍不懂該如何寫VBA

是否可請教各位先進，教導一下 SUMIFS VBA 語法

感恩!

作者: jsc0518 時間: 2017-5-30 18:37

我是直接寫成

Sub S40()
For I = 5 To [a65536].End(3).Row
Cells(I, "G") = SumIfs(Cells(I, "R"), Cells(I, "O"), Cells(I, "A"), Cells(I, "P"), Cells(I, "B"), Cells(I, "Q"), Cells(I, "C"))
Next
End Sub

但有錯誤

[attach]27252[/attach]

作者: ML089 時間: 2017-5-30 20:54

回復 2# jsc0518

'Cells(I, "G") = SumIfs(Cells(I, "R"), Cells(I, "O"), Cells(I, "A"), Cells(I, "P"), Cells(I, "B"), Cells(I, "Q"), Cells(I, "C"))
改為
Cells(I, "G") = Application.SumIfs([R5:R50], [O5:O50], Cells(I, "A"), [P5:P50], Cells(I, "B"), [Q5:Q50], Cells(I, "C"))

作者: jsc0518 時間: 2017-5-31 07:50

回復 3# ML089

感謝您的回覆，可以使用!
再次謝謝!

作者: s3526369 時間: 2020-2-25 09:21

回復 3# ML089

請問這樣的函數要怎麼改
=SUMIF(入庫單!$O$2:$O$1001,倉庫庫存!A4,入庫單!$R$2:$R$1001)
感謝~

作者: rouber590324 時間: 2020-2-26 10:11

= Application.SumIf(Sheet1.Range("Q

"), Sheet1.Cells(4, 1), Sheet1.Range("R:R"))

作者: s3526369 時間: 2020-3-9 13:09

回復 6# rouber590324

感謝!!~~

作者: jsc0518 時間: 2021-12-7 16:46

回復 3# ML089
Dear ML,
抱歉，可以再幫我解一下疑惑嗎？

此段為excel函數，我想把他轉VBA，因有跨工作表的問題，所以自己run不出來
=SUMIFS(ROUND!M2:M10000,ROUND!A2:A10000,A2)

下方是我改過的語法，但有問題

Sub Sumifs()

For I = 2 To [a65536].End(3).Row
Cells(I, "G") = Application.SumIfs((Sheets("ROUND").[M2:M10000]), (Sheets("ROUND").[A2:A10000]), Cells(I, "A")
Next

End Sub

[attach]34470[/attach]

作者: samwang 時間: 2021-12-7 19:06

回復 8# jsc0518

少了一個 )
Cells(I, "G") = Application.SumIfs((Sheets("ROUND").[M2:M10000]), (Sheets("ROUND").[A2:A10000]), Cells(I, "A"))

作者: jsc0518 時間: 2021-12-7 19:47

回復 9# samwang
Dear samwang,
晚上好！感謝您的回覆歐，剛用了測試OK。

再請教您一個問題
我若改成 Sumif (語法如下) ，但會發生 400 錯誤，是否可幫我解惑，哪個語法有問題
Thank you very much.

Sub Sumif()

For I = 2 To [a65536].End(3).Row
Cells(I, "G") = Application.Sumif(工作表2.Range("A"), 工作表3.Cells(I, "A"), 工作表2.Range("M:M"))
Next

End Sub

[attach]34473[/attach]

作者: samwang 時間: 2021-12-7 20:47

回復 10# jsc0518

方便附上檔案且說明一下問題需求，謝謝

作者: jsc0518 時間: 2021-12-7 21:08

本帖最後由 jsc0518 於 2021-12-7 21:13 編輯

回復 11# samwang

我希望將 F2欄位Sumif公式轉成VBA
[attach]34475[/attach]

[attach]34476[/attach]

作者: samwang 時間: 2021-12-7 21:43

回復 12# jsc0518

請測試看看，謝謝
Sub Sumif()
For I = 2 To [a65536].End(3).Row
Cells(I, "G") = Application.Sumif(Sheets("ROUND").Range("A2:A65536"), Cells(I, "A"), Sheets("ROUND").Range("M2:M65536"))
Next
End Sub

作者: jsc0518 時間: 2021-12-8 08:57

回復 13# samwang
Dear samwang,
正常可以使用，感謝您！ ^^

作者: samwang 時間: 2021-12-8 11:36

回復 14# jsc0518

如果資料多的話，13#會比較慢一點，謝謝

作者: jsc0518 時間: 2021-12-8 15:24

本帖最後由 jsc0518 於 2021-12-8 15:26 編輯

回復 15# samwang
Dear samwang,
您好！資料一多真的會跑很久，另外我套用的EXCEL檔案內有我有寫很多的函數，在執行sumif、sumifs這2個VBA語法都跑很慢，是否可以再簡化語法或是運算時間縮短呢？
Thank you.

Excel 執行VBA 時的畫面
[attach]34483[/attach]

Sub Sumif()

For I = 2 To [a65536].End(3).Row
Cells(I, "H") = Application.Sumif(Sheets("ROUND").Range("A2:A65536"), Cells(I, "A"), Sheets("ROUND").Range("M2:M65536"))
Next

End Sub

Sub Sumifs()

For I = 2 To [a65536].End(3).Row
Cells(I, "G") = Application.Sumifs((Sheets("ROUND").[M2:M10000]), (Sheets("ROUND").[A2:A10000]), Cells(I, "A"))
Next

End Sub

作者: samwang 時間: 2021-12-8 15:56

回復 samwang
Dear samwang,
您好！資料一多真的會跑很久，另外我套用的EXCEL檔案內有我有寫很多的函數 ...
jsc0518 發表於 2021-12-8 15:24

請問方便可以附上檔案嗎?
以利測試用
謝謝

作者: samwang 時間: 2021-12-8 17:09

回復 samwang
Dear samwang,
您好！資料一多真的會跑很久，另外我套用的EXCEL檔案內有我有寫很多的函數 ...
jsc0518 發表於 2021-12-8 15:24

因為沒有附檔，只能用12#的檔案去寫，請測試看看，謝謝

Sub test()
Dim Arr, Brr, xD, i&, T$
Set xD = CreateObject("Scripting.Dictionary")
Tm = Timer
Arr = [ROUND!a1].CurrentRegion
For i = 2 To UBound(Arr)
T = Arr(i, 1)
xD(T) = Val(xD(T)) + Val(Arr(i, 13))
Next
With Sheets("Sumifs")
Brr = .Range(.[a1], .[a65536].End(3))
For i = 1 To UBound(Brr)
Brr(i, 1) = xD(Arr(i, 1) & "")
Next
.[g1].Resize(UBound(Brr)) = Brr
End With
MsgBox Timer - Tm
End Sub

作者: jsc0518 時間: 2021-12-8 20:56

回復 18# samwang
Dear samwang,
晚上好！感謝您的熱心回復。因主要檔案資料內容為公司的資料，所以僅能用示範檔案給您，抱歉 ><"
剛剛是RUN了一下，有錯誤訊息！

[attach]34484[/attach]

作者: samwang 時間: 2021-12-8 21:01

回復 samwang
Dear samwang,
晚上好！感謝您的熱心回復。因主要檔案資料內容為公司的資料，所以僅能用示 ...
jsc0518 發表於 2021-12-8 20:56

我測試沒問題如附件，請測試看看，謝謝

作者: jsc0518 時間: 2021-12-8 22:03

回復 20# samwang
Dear samwang,
您好！您所附的檔案測試正常。我改了您的VBA至我的EXCEL上，有問題，畫面如下

紅字是我改過的
----------------------------------------------------------------------
Sub test22()
Dim Arr, Brr, xD, i&, T$
Set xD = CreateObject("Scripting.Dictionary")
Tm = Timer
Arr = [繳庫量!g1].CurrentRegion
For i = 2 To UBound(Arr)
T = Arr(i, 1)
xD(T) = Val(xD(T)) + Val(Arr(i, 13))
Next
With Sheets("Analysis")
Brr = .Range(.[a1], .[a65536].End(3))
For i = 1 To UBound(Brr)
Brr(i, 1) = xD(Arr(i, 1) & "")
Next
.[g1].Resize(UBound(Brr)) = Brr
End With
MsgBox Timer - Tm
End Sub
----------------------------------------------------------------------

[attach]34486[/attach]

[attach]34487[/attach]

作者: jsc0518 時間: 2021-12-10 11:18

回復 20# samwang
Dear samwang,
早上好，可以幫我看一下附加檔案嗎？
我有實際依據需求，調整了excel欄位位置，以及修改過您的VBA
可是在工作表"Analysis"內的H欄位卻是帶出空白
可以幫我看內容哪裡有誤嗎？
感恩！

[attach]34499[/attach]

作者: samwang 時間: 2021-12-10 13:08

回復 22# jsc0518

Arr-->Brr
Brr(i, 1) = xD(Brr(i, 1) & "")

作者: jsc0518 時間: 2021-12-10 15:03

回復 23# samwang
Dear samwang,
OK，可以用。在與您請教一下，執行後H1欄位的"VBA"都會變成空白。
是否可以修訂？

[attach]34501[/attach]

[attach]34502[/attach]

作者: samwang 時間: 2021-12-10 15:35

回復 samwang
Dear samwang,
OK，可以用。在與您請教一下，執行後H1欄位的"VBA"都會變成空白。
是否可 ...
jsc0518 發表於 2021-12-10 15:03

.[h1].Resize(UBound(Brr)) = Brr
.[h1] = "VBA"
End With

作者: jsc0518 時間: 2021-12-10 20:56

回復 25# samwang
Dear samwang,
Thank you so much.感謝您囉！ ^_^

作者: mdr0465 時間: 2022-4-26 10:30

本帖最後由 mdr0465 於 2022-4-26 10:31 編輯

回復 26# samwang

samwang你好
我借用你的完成品修改一下, 將sumif變成橫向的,第一次運算是成功的, 但不知為何當第二次運算時就會失敗, 請幫忙查看並指點我錯在那里,

謝謝

作者: samwang 時間: 2022-4-26 11:57

回復 samwang

samwang你好
我借用你的完成品修改一下, 將sumif變成橫向的,第一次運算是成功的, 但 ...
mdr0465 發表於 2022-4-26 10:30

回填資料時要先舊的答案先清除，我想你應該知道如何做，寫得很好，自己在試試看，謝謝

作者: mdr0465 時間: 2022-4-26 12:13

回復 28# samwang

SAMWANG師兄,你好

謝謝你的指導, 我想想怎樣在回填資料時先舊的答案先清除,

謝謝你耐心教導, 謝謝

作者: samwang 時間: 2022-4-26 12:19

回復 29# mdr0465

另外，資料回填時也有錯位問題，多善用F8就可知道問題在哪裡，謝謝

作者: mdr0465 時間: 2022-4-26 13:05

本帖最後由 mdr0465 於 2022-4-26 13:07 編輯

回復 30# samwang

SAMWANG 師兄,你好

我都看到資料回填時也有錯位問題, 但我用愚蠢的方法我修正了(請看以下紅字的地方), 這一點我始終不明白當中是為何這樣設定就可以解決, 請SAMWANG 指教

.[A20:A21].EntireRow.Clear
.[B20].Resize(1, xD2.Count) = xD2.Keys
Crr = .[B20].CurrentRegion
For x = 1 To UBound(Crr, 2)
Crr(1, x) = xD(Crr(1, x) & "")
Next

.[B21].Resize(UBound(Crr), xD.Count - 4) = Crr
.[A20] = "XX"
.[A21] = "YY"

作者: samwang 時間: 2022-4-26 13:28

回復 31# mdr0465

請看附件，謝謝

作者: mdr0465 時間: 2022-4-26 14:27

本帖最後由 mdr0465 於 2022-4-26 14:33 編輯

回復 32# samwang

samwang師兄,你好

受教了, 原來是我在運算上做了多餘的事情,

萬分感激指導我的錯處, 日後如果不懂的地方,再看samwang 師兄請教

謝謝

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