将excel sumifs公式转换为vba代码

I'm trying to do a SUMIFS calculation in VBA. It works fine when I enter it on the spreadsheet, but when I try to convert it to VBA, it doesn't seem to work.

我正在尝试在VBA中进行SUMIFS计算。当我在电子表格中输入它时,它工作正常,但当我尝试将其转换为VBA时,它似乎不起作用。

Sheets("Master").Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row).Formula = _
    "=SUMIFS(Input!C32,Input!C37,Master!C1,Input!C31,Master!R1C)"

This is the snippet of code (originally in a comment):

这是代码片段(最初在评论中):

Dim LastRow As Long 
Dim rw As Long 
LastRow = Range("A" & Rows.Count).End(xlUp).Row 
For rw = 2 To LastRow 
  Sheets("Master").Cells(rw, 2).Value = Application.WorksheetFunction.SumIfs(Sheets("Input").Range("AF:AF"), Sheets("Input").Range("AK:AK"), Sheets("Master").Range("A:A"), Sheets("Input").Range("AE:AE").Sheets("Master").Range("B2"))  
Next

1 个解决方案

#1

You aren't specifying the values you want to lookup in your criteria1. You have to specify a value, not a range.

您没有在criteria1中指定要查找的值。您必须指定一个值,而不是范围。

Your sum range is fine.

你的总和范围很好。

Sheets("Input").Range("AF:AF")

Your criteria range1 is fine.

你的标准范围1很好。

Sheets("Input").Range("AK:AK")

Your criteria1 needs to be a value, not a range.

您的标准1需要是一个值,而不是一个范围。

Use this Sheets("Master").Range("A2").value instead of Sheets("Master").Range("A:A")

使用此表格(“主”)。范围(“A2”)。值而不是表格(“主”)。范围(“A:A”)

Obviously you can replace the 2 in the criteria1 with a variable if you need to to get your loop to work.

显然,如果需要让循环工作,可以用criteria变量替换criteria1中的2。

#1