autofilter and subtotal not playing nice 1

[sarahnade]

I would like to count the number of cells still visible after an autofilter. I know the Excel SUBTOTAL function will do this. I must be doing something incorrectly because this isn't happening for me.

My code looks like this:

...
Range("Fund_table").AutoFilter Field:=2, Criteria1:=Id, VisibleDropDown:=False
            If Application.Evaluate("SUBTOTAL(3, FundId)") = 0 Then
...

Fund_table is a table imported from Access, Id is the integer I'm looking for, and FundId is a named range (a column within Fund_table). I get no errors from the code, but the result of the subtotal is the total number of rows, and not just the visible ones. Either I'm not applying the autofilter correctly or subtotal is counting more than it should.

Any help would be appreciated.

Sarah
-don't panic, I'm mostly harmless-

 

[SkipVought]

Do you have a RANGE Named FundId, or is this merely a Heading value?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, you ought to be able to do this function, on the sheet and get a valid answer...
[tt]
=SUBTOTAL(3, FundId)
[/tt]
If NOT, then you have an error in your function.

Fix THAT first!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sarahnade]

Thanks for the quick reply!

FundId is actually a named range and not just a heading.

Also, putting the subtotal on my sheet and manually doing an autofilter produces the correct number. So it must be my code. But I don't know what.

Halp!

Sarah
-don't panic, I'm mostly harmless-

 

[SkipVought]

3 does a COUNTA.

2 does a COUNT.

Which do you want?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sarahnade]

Either one should produce the same result as there are no empty cells in the table.

Sarah
-don't panic, I'm mostly harmless-

 

[SkipVought]

how about

Application.SUBTOTAL(3, [FundId]) = 0

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

I'd use the WorksheetFunction property:

...
Range("Fund_table").AutoFilter Field:=2, Criteria1:=Id, VisibleDropDown:=False
If Application.WorksheetFunction.Subtotal(3, Range("FundId")) = 0 Then
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sarahnade]

Skip: The code runs through quickly but only gives me #VALUE errors in the cells running the code.

PHV: I just get the same results as before.



Sarah
-don't panic, I'm mostly harmless-

 

[sarahnade]

But thanks for the suggestions! Keep 'em coming!

Sarah
-don't panic, I'm mostly harmless-

 

[SkipVought]

Please post your UDF complete code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sarahnade]

Public Function NewPIRR(FundOrIndex As String, _
                     Id As String, _
                     PeriodLength As Double, _
                     PeriodEndDate As Date) _
                As Variant

    Application.ScreenUpdating = False

    Dim IdRange As String
    Dim ReturnDateRange As String
    Dim ReturnTable As String
    Dim ReturnRange As String
    Dim FieldNumber As Integer
    

        
    ' Assign appropriate (fund or index) variables -----------------------------
    Select Case FundOrIndex
        Case "fund"
            Range("Fund_table").AutoFilter Field:=2, Criteria1:=Id, VisibleDropDown:=False
            ' If Application.Evaluate("SUBTOTAL(3, FundId)") = 0 Then
            If Application.WorksheetFunction.Subtotal(3, Range("FundId")) = 0 Then
                NewPIRR = "#FUND ID NOT FOUND!"
                Exit Function
            Else
                IdRange = "FundId"
                ReturnDateRange = "FundReturnDate"
                ReturnRange = "FundReturn"
                ReturnTable = "Fund_table"
                FieldNumber = 2
            End If
        Case "index"
            Range("Index_table").AutoFilter Field:=1, Criteria1:=Id, VisibleDropDown:=False
            ' If Application.Evaluate("SUBTOTAL(3, IndexId)") = 0 Then
            If Application.WorksheetFunction.Subtotal(3, Range("IndexId")) = 0 Then
                NewPIRR = "#INDEX ID NOT FOUND!"
                Exit Function
            Else
                IdRange = "IndexId"
                ReturnDateRange = "IndexReturnDate"
                ReturnRange = "IndexReturn"
                ReturnTable = "Index_table"
                FieldNumber = 1
            End If
        Case Else
            NewPIRR = "#SELECT FUND OR INDEX!"
            Exit Function
    End Select

There is a lot more code after this, but these are the relevant bits.

Sarah
-don't panic, I'm mostly harmless-

 

[SkipVought]

What's the PURPOSE of setting all these variables???
[tt]
IdRange
ReturnDateRange
ReturnRange
ReturnTable
FieldNumber
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Loomah]

wouldn't

range(indexid).specialcells(xlcelltypevisible).count

achieve the objective of counting the visible cells?
subtract 1 if the named range includes the header

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[sarahnade]

Skip, there's different functionality based on whether we're dealing with an index or a fund. But that's not really relevant to this.

Loomah, thanks for the suggestion. It didn't work. BUT! That tells me it's definitely a problem with my autofilter.

Is there something I'm missing in order to create an autofiler? It looks like I have the syntax right, it's just missing something to tell it to GO!

Sarah
-don't panic, I'm mostly harmless-

 

[SkipVought]

How are you calling this UDF?

I've reconstructed in a sheet and it works.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sarahnade]

It is used as an Excel addin and called from an individual cell.

"=NewPIRR("fund",$A10,3,PeriodEndDate)"

I can't believe it works for you. I will try recreating this section on a new sheet and test the results. Thanks.

Sarah
-don't panic, I'm mostly harmless-

 

[SkipVought]

Your problem is, " called from an individual cell."

A UDF, called from a cell, cannot change anothing else in the workbook, except the return value.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

It worked for me because I called it, NOT from a cell, which did not work, but from a procedure.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sarahnade]

Really? Wow. Not at all? Ok, I didn't know that.

I guess I need to find a different way to approach this.

Thanks very much for your help.

Sarah
-don't panic, I'm mostly harmless-