Application.WorksheetFunction.Sum在过滤结果VBA上不起作用 [英] Application.WorksheetFunction.Sum is not working on filtered results VBA

问题描述

我正在尝试使用WorksheetFunction.Sum来对结果中所有行的相同字段求和，但是它似乎并没有这样做。它不会抛出错误，它只是不总结任何东西。我知道一些事实，一些行应该抛出超过1行的结果。

这是我要做的：

 对于BomCodes中的每个代码
 
与InventorySheet 
 .AutoFilterMode = False 
 .Range（B1）。AutoFilter Field：= 2，Criteria1：=Project
 .Range（D1）。AutoFilter字段：= 4，Criteria1：=ContractNumber
 .Range（N1）AutoFilter字段： = 14，Criteria1：=Code
 .Range（Q1）。AutoFilter字段：= 17，Criteria1：=> 0
结束
 
'通过过滤的库存进行搜索
设置rangeFilteredInventory = InventorySheet.Range（Q2：Q& Cells（Rows.Count，Q）。End（xlUp）.Row）
 
'获得所有结果的总和
使用Invento rySheet 
 TotalQty = WorksheetFunction.Sum（rangeFilteredInventory.SpecialCells（xlCellTypeVisible））
如果TotalQty<> 0然后
 Debug.Print TotalQty，vbNewLine，vbNewLine 
结束如果
结束
下一个代码
  

我没有使用库存表，尝试使用整个

Applicaiton.WorksheetFunction.Sum ，

设置rangeFilteredInventory = InventorySheet.Range（Q2：Q& Cells（Rows.Count，Q ）.End（xlUp）.Row）

循环遍历所有但如果我评论If结构，它总是显示0。 p>

更改

设置rangeFilteredInventory = InventorySheet.Range（Q2：Q& Rows.Count）.End（xlUp）.Row

即使是第一条记录也不能访问

还有

设置rangeFilteredInventory = InventorySheet.Range（Q& Rows.Count）.End（xlUp ）.Row

它不访问冷杉t记录。

所以，我的想法是我把范围错了。它应该总结在该表中的所有Q。

如果我在该工作表上做一个宏

.AutoFilterMode = False
.Range（B1）。AutoFilter字段：= 2，条件1：=5522970
.Range（D1）。AutoFilter Field：= 4，Criteria1：=0008621302140U
.Range（N1）。AutoFilter字段：= 14，Criteria1：=LVE70001372
.Range（Q1）。AutoFilter字段：= 17，Criteria1：=> 0
结束

它返回多个记录。

对不起，如果我在新的东西在错误的方式，我是新来的VBA，我正在自己学习。

编辑：

我将SUM转换为小计，虽然它执行循环，总数仍然是0.我使用的部分答案BK201给了我改进一些东西。

 对于每个代码在BomCodes 
 Debug.Print抓取代码：代码，vbNewLine 
与InventorySheet 
 .AutoFilterMode = False 
 LRowOnQ = .Columns（Q）。End（xlUp）.Row 
 .Range（B1）。AutoFilter字段：= 2，Criteria1：=Project
 .Range（D1）。AutoFilter字段：= 4，Criteria1：=ContractNumber
 .Range（N1）。AutoFilter字段：= 14，Criteria1：=Code
 .Range（Q1）。AutoFilter字段：= 17，Criteria1：=> 0
设置rangeFilteredInventory = .Range（Q2：Q& LRowOnQ）
结束
 
'通过过滤的库存进行搜索
 
'获取所有结果的总和
'与InventorySheet 
 TotalQty = WorksheetFunction.Subtotal（9，rangeFilteredInventory.SpecialCells（xlCellTypeVisible ））
如果TotalQty<> 0然后
 Debug.Print TotalQty，vbNewLine，vbNewLine 
 End If 
下一个代码
  

解决方案

在使用查找最后一行时，始终应用与的逻辑。基本上，请注意您要查找的工作表中的哪一行。更明确我认为部分错误从简单的坏编码风格上升，而不是坏的代码，因为方法是声音，虽然结果不存在。

为了更好的编码，也可能是一个解决方案，请参阅以下代码和以后的注释。

 对于BomCodes中的每个代码
使用InventorySheet 
 .AutoFilterMode = False 
 LRowOnQ = .Columns（Q）。End（xlUp）.Row 
 .Range（B1）。AutoFilter字段：= 2，条件1： =Project
 .Range（D1）。AutoFilter Field：= 4，Criteria1：=ContractNumber
 .Range（N1）。AutoFilter Field：= 14，Criteria1：=代码
 .Range（Q1）。AutoFilter字段：= 17，Criteria1：=> 0
设置rangeFilteredInventory = .Range（Q2：Q& LRowOnQ）
结束
 
'获取所有结果的总和
 TotalQty = WorksheetFunction.Sum（rangeFilteredInventory.SpecialCells（xlCellTypeVisible））
如果TotalQty<> 0然后
 Debug.Print TotalQty，vbNewLine，vbNewLine 
 End If 
下一个代码
  

两件事：

首先，注意变量LRowOnQ。与你的代码不同的是，你使用 Cells（Rows.Count ... 而没有明确规定应该在哪里，只是因为 InventorySheet 之前并不意味着该变量将从那里获取，如果从另一个表单中调用宏，最后一行从活动表单中获取，则返回一个错误的值。

正确的处理方法是将其锁定到 InventorySheet ，列Q。这样，价值不会来自一个模糊的来源，这也促进了更好的编码风格，如 Set ting rangeFilteredInventory 现在被减少到更可读的代码。

另外，请注意该位置，它是在 AutoFilter 被应用之前设置的原因很简单：在过滤器应用后放置它将检查过滤列的最后一行，而不是数据表的最后一行。提供测试代码以供参考。

  Sub Test（）
 Dim Rng As Range 
设置Rng = ActiveSheet.Columns（Q）
 Debug.Print Rng.Cells（Rows.Count，1）.End（xlUp）.Row 
 End Sub 
  

有很多原因返回过滤表的最后一行比整个未过滤的表更麻烦，但我不会在这里进入。但是，如果相反的是你想要的，只需将所有的 AutoFilter 应用。

第二个注意我如何使用InventorySheet 删除次要的。我在代码中看到，您必须使用InventorySheet 声明两次，但第二个程序段根本没有用。由于 rangeFilteredInventory 已经被声明，所以没有必要将它包含在中，使用块。

现在，即使修改了编码风格，您的方法也可以使用一些工作。我，一个，第二个使用 SUBTOTAL 而不是 SUM 。不过，我不会假设这是必要的，因为您可能会有所不同，或者您可能希望将其用于其他原因。

请测试上面的内容，让我们知道结果。

I'm trying to use WorksheetFunction.Sum to sum the same field on all rows in the result, however, it doesn't seem to be doing it. It doesn't throw an error, it just doesn't sums anything. I know for a fact some rows should throw more than 1 row result.

This is what I'm trying to do:

For Each Code In BomCodes

                        With InventorySheet
                            .AutoFilterMode = False
                            .Range("B1").AutoFilter Field:=2, Criteria1:="Project"
                            .Range("D1").AutoFilter Field:=4, Criteria1:="ContractNumber"
                            .Range("N1").AutoFilter Field:=14, Criteria1:="Code"
                            .Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
                        End With

                        'Do a search through the filtered inventory
                        Set rangeFilteredInventory = InventorySheet.Range("Q2:Q" & Cells(Rows.Count, "Q").End(xlUp).Row)

                            'Get the sum of all results
                            With InventorySheet
                            TotalQty = WorksheetFunction.Sum(rangeFilteredInventory.SpecialCells(xlCellTypeVisible))
                             If TotalQty <> 0 Then
                                Debug.Print TotalQty, vbNewLine, vbNewLine
                             End If
                            End With
                         Next Code

I've tried without using With InventorySheet, using the whole

Applicaiton.WorksheetFunction.Sum,

Set rangeFilteredInventory = InventorySheet.Range("Q2:Q" & Cells(Rows.Count, "Q").End(xlUp).Row)

Loops through all BUT If I comment the If structure, it always show 0.

Changing for

Set rangeFilteredInventory = InventorySheet.Range("Q2:Q" & Rows.Count).End(xlUp).Row

It doesn't access even the first record

And also

Set rangeFilteredInventory = InventorySheet.Range("Q" & Rows.Count).End(xlUp).Row

It doesn't access the first record either.

So, my idea is that I'm taking the range wrong. It should sum up all the "Q's" in that sheet.

If i make a macro on that sheet that does

With Worksheets("sbom")
                            .AutoFilterMode = False
                            .Range("B1").AutoFilter Field:=2, Criteria1:="5522970"
                            .Range("D1").AutoFilter Field:=4, Criteria1:="0008621302140U"
                            .Range("N1").AutoFilter Field:=14, Criteria1:="LVE70001372"
                            .Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
 End With

It returns multiple records.

I'm sorry if I'm new anything in there in the wrong way, I'm new to VBA and i'm learning on my own.

EDIT:

I switched SUM for Subtotal and although it does the loop, TotalQty is still 0. I used part of the answer BK201 gave me to improve some things.

For Each Code In BomCodes
                       Debug.Print "Grabbed Code:", Code, vbNewLine
                        With InventorySheet
                            .AutoFilterMode = False
                             LRowOnQ = .Columns("Q").End(xlUp).Row
                            .Range("B1").AutoFilter Field:=2, Criteria1:="Project"
                            .Range("D1").AutoFilter Field:=4, Criteria1:="ContractNumber"
                            .Range("N1").AutoFilter Field:=14, Criteria1:="Code"
                            .Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
                            Set rangeFilteredInventory = .Range("Q2:Q" & LRowOnQ)
                        End With

                        'Do a search through the filtered inventory

                            'Get the sum of all results
                           ' With InventorySheet
                            TotalQty = WorksheetFunction.Subtotal(9, rangeFilteredInventory.SpecialCells(xlCellTypeVisible))
                                If TotalQty <> 0 Then
                                    Debug.Print TotalQty, vbNewLine, vbNewLine
                                End If
                         Next Code

解决方案

When working with finding last rows, always apply the logic of With. Basically, be mindful of which row in which sheet you want to find. Be more explicit. I think that part of the error rises from simple bad coding style, not bad code, as the approach is sound though the results are not there.

In the interest of better coding, and maybe a solution, please see the following code and the comments after.

For Each Code In BomCodes
    With InventorySheet
        .AutoFilterMode = False
        LRowOnQ = .Columns("Q").End(xlUp).Row
        .Range("B1").AutoFilter Field:=2, Criteria1:="Project"
        .Range("D1").AutoFilter Field:=4, Criteria1:="ContractNumber"
        .Range("N1").AutoFilter Field:=14, Criteria1:="Code"
        .Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
        Set rangeFilteredInventory = .Range("Q2:Q" & LRowOnQ)
    End With

    'Get the sum of all results
    TotalQty =WorksheetFunction.Sum(rangeFilteredInventory.SpecialCells(xlCellTypeVisible))
    If TotalQty <> 0 Then
        Debug.Print TotalQty, vbNewLine, vbNewLine
    End If
Next Code

Two things:

First, notice the variable LRowOnQ. The difference from the your code is that you used Cells(Rows.Count... without a clear specification of where it's supposed to be taken. Just because InventorySheet precedes it doesn't mean the variable will be taken from there. If your macro is being called from another sheet and the last row is taken from the active sheet, it will return a wrong value.

The proper way to deal with this is, as seen above, to lock it to InventorySheet, column Q. That way, the value will not come from a vague source. This also promotes better coding style, as Setting rangeFilteredInventory now is reduced to more readable code.

Also, notice the placement. It was set before the AutoFilter is applied. The reason is quite simple: placing it after the filter is applied will check the last row of the filtered column and not the last row of the data table. Test code is provided for reference.

Sub Test()
    Dim Rng As Range
    Set Rng = ActiveSheet.Columns("Q")
    Debug.Print Rng.Cells(Rows.Count, 1).End(xlUp).Row
End Sub

There are many reasons why returning the last row of a filtered table is more troublesome than the whole unfiltered table, but I won't get into that here. However, if the reverse is what you want, just place it after all the AutoFilters are applied.

Second, notice how I removed the secondary With InventorySheet. I see in your code that you had to declare With InventorySheet twice, but the second block has no use for it at all. Since rangeFilteredInventory has been declared already, there's no need to include it in a With block.

Now, even with the coding style fixed, your approach can use some work. I, for one, second the use of SUBTOTAL instead of SUM. However, I won't assume that this is what's necessary, as the case may be different for you or perhaps you want to use this for some other reason instead.

Please test the above and let us know of the results.

这篇关于Application.WorksheetFunction.Sum在过滤结果VBA上不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！