Application.WorksheetFunction.Sum is not working o

Question

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

Answer 1

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.