AdvancedFilter in VBA, Help!

[MikeMcCollister]

I am trying to do some advanced filtering so that I can sum some columns. My test code does not work. First when I try to set the criteria cell VBA just errors out. If I skip that and try to Call rangeTable.AdvancedFilter it does nothing.

Function FilterTest(theMonth As String, category As String) As Variant
    Dim rangeTable As Range
    Dim rangeCriteria As Range
    Dim rangeFiltered As Range
   
    With Application.ThisWorkbook.Worksheets("Sheet1")
        Set rangeTable = .Range("$A$5:$L$1001")
        Set rangeCriteria = .Range("$A$3:$L$4")
    End With
   
    ' set category to filter on
    ' !!! does not work
    rangeCriteria(2, 4) = category
   
    ' set month to filter on
    ' !!! add code here
   
    ' does nothing
    Call rangeTable.AdvancedFilter(xlFilterInPlace, rangeCriteria)
   
    ' FilterTest = columnE - columnG + columnH

    ' clear AdvancedFilter
    ' !!! add code here
   
    FilterTest = 1.23
End Function

I think that I am missing something fundamental. Any help will be appreciated.

Thanks,

Mike

 

[MikeMcCollister]

Looks like I can't do this in a function as per https://support.microsoft.com/en-us...n-of-limitations-of-custom-functions-in-excel.

Function's can't change values of cell or the environment of Excel. Dang.

Oh well.

Mike

 

[mikerickson]

Not so fast. You are right about this not being doable with a normal UDF, but there are abnormal UDFs.
I'm curious about your criteria range. It looks like you have a column (criteria) for each column in your data range and the function only controls one of those criteria.

In words, what are you trying to do. Show only those records that match the category passed as an argument, or do the other columns have other restrictions that you express in cells A4:L4?

 

[MikeMcCollister]

Not so fast. You are right about this not being doable with a normal UDF, but there are abnormal UDFs.
I'm curious about your criteria range. It looks like you have a column (criteria) for each column in your data range and the function only controls one of those criteria.

In words, what are you trying to do. Show only those records that match the category passed as an argument, or do the other columns have other restrictions that you express in cells A4:L4?

I don't want to show only those records, I want to filter then do some math and then unfilter. For example, I want to know the total for a category that has "B: Gas", for a given month, where the total is "detail - debit + credit".

I can do this now using sumif and sumfis but I'm investigating on whether I can use filtering to make this faster.

Regards,

Mike

 

[mikerickson]

I think I missed something when I spoke earlier
Hiding rows triggers a calculation and my workaround leads to a calculation loop.

But to address your last point, making cells visible or un takes a relatively large amount of time. If you have a solution that is based on SumIfs, that is probably faster than anything involving Advanced Filter.

 

[MikeMcCollister]

Thanks. I figured that but I wanted to make sure. I'm just looking at alternative way to do things these days.

Regards,

Mike

 

[mikerickson]

IF you like AdavancedFiler, have you looked at the database functions DCOUNT, DMAX, etc. They are a bit clunky, but if you understand criteria ranges, they can be useful.

 

[MikeMcCollister]

IF you like AdavancedFiler, have you looked at the database functions DCOUNT, DMAX, etc. They are a bit clunky, but if you understand criteria ranges, they can be useful.

Thanks.

DSUM looks like it has potential but within a function I would have to write to a sheet to get the criteria. So, sumif and sumifs is the way to go for me right now.

Mike