返回过滤/可见列表的总和值，excel VBA

Return sum value of filtered/visible list, excel VBA

提问by user1414559

I have two workbooks: "TEST" and "Report". I have used the following code:

我有两本工作簿：“测试”和“报告”。我使用了以下代码：

MsgBox Sum_Visible_Cells(Worksheets("Raw Data").Range("M2:M" & LastRow(Worksheets("Raw Data"))))

and it returns the sum value in a messagebox. I have also been able to return the sum in a cell on the worksheet "Raw Data" in the workbook "TEST".

并在消息框中返回总和值。我还能够返回工作簿“TEST”中工作表“原始数据”的单元格中的总和。

How can I make excel return the value in worksheet "Sheet1" , range "L9" on the workbook "Report"?

如何让excel返回工作簿“报告”上工作表“Sheet1”，范围“L9”中的值？

回答by chris neilsen

You don't need vba to do this. In cell Sheet1!L9enter the formula

你不需要 vba 来做到这一点。在单元格中Sheet1!L9输入公式

=SUBTOTAL(109,'Raw Data'!$M:$M)

To do it in VBA use

要在 VBA 中使用

Application.Workbooks("Report.xlsm").Worksheets("Sheet1").Range("L9") = "Result of your function"

Note: you must include file extension, and the workbook must be open.

注意：您必须包含文件扩展名，并且工作簿必须是打开的。

EDIT:
Sum_Visible_Cellsis not a worksheet function.

编辑：
Sum_Visible_Cells不是工作表函数。

try this (assuming the code is in workbook Test)

试试这个（假设代码在工作簿中Test）

Application.Workbooks("Report.xlsm").Worksheets("Sheet1").Range("L9") = _
    Application.WorksheetFunction.Subtotal(109, ThisWorkbook.Worksheets("Raw Data").Columns(13))

From your comment Range("M2:M)I guess you want to exclude row 1 from the total.
So long as M1is not numeric summing the whole column will be fine. If row 1 isnumeric and you want to exclude it try

根据您的评论，Range("M2:M)我猜您想从总数中排除第 1 行。
只要M1不是数字求和整列就可以了。如果第 1 行是数字并且您想排除它，请尝试

Dim rng as Range
With ThisWorkbook.Worksheets("Raw Data")
    Set rng = .Range(.Cells(2,13), .Cells(.Rows.Count, 13).End(xlUp))
End With 
Application.Workbooks("Report.xlsm").Worksheets("Sheet1").Range("L9") = _
    Application.WorksheetFunction.Subtotal(109, rng)

回答by Cynicszm

Does it have to be done in the order you describe?

是否必须按照您描述的顺序进行？

If you use the suggestion in the attached link "VBA to get value from closed file"to add the GetValue function (make public) to retrieve the information from the Test Workbook you would have:

如果您使用附加链接“VBA 从关闭的文件中获取值”中的建议添加 GetValue 函数（公开）以从测试工作簿中检索信息，您将拥有：

In TEST Workbook:

在测试工作簿中：

• Create a range containing your existing formula e.g. Sheet1, Cell A1Sum_Visible_Cells(Worksheets("Raw Data").Range("M2:M" & LastRow(Worksheets("Raw Data")))) formula

• 创建一个包含现有公式的范围，例如 Sheet1, Cell A1Sum_Visible_Cells(Worksheets("Raw Data").Range("M2:M" & LastRow(Worksheets("Raw Data")))) 公式

In REPORT Workbook use the GetValue() function in Sheet1 Cell L9 :

在 REPORT 工作簿中使用 Sheet1 Cell L9 中的 GetValue() 函数：

=GetValue(p, f, s, a)

where

在哪里

• p = "Path to TEST Workbook"
• f = "TEST.xls"
• s = "Sheet1"
• a = "A1"

• p = "测试工作簿的路径"
• f = "TEST.xls"
• s = "工作表 1"
• a = "A1"