Autofilter not working in Functions

[144725]

My workbook has two sheets. Status and worklog sheets. I am trying to get the count of completed work of various phases in the status sheet which reads the data from worklog. Each phase is tracked in a column in worklog sheet. My Worklog look like this,
Col A: Workstream (Values 0-8)
Col B: Iteration (Values 0 -10)
Col H: Analysis (Values Complete, WIP, Yet To Start)
Col I: Clarification (Values Complete, WIP, Yet To Start)
Col J: Design (Values Complete, WIP, Yet To Start) etc.,

In Status sheet, I need the count of Iterations for which the above said phases are completed. So i wrote a function

Function Status(byVal Workstream as Integer, byVal Iteration as Integer, byVal phase as String) Ex: Status(1,5,"Design")

The function should autofilter the worklog with workstream, iteration and phase and return the no. of rows. The column to autofilter for phase is determined by a switch method.
But the ultimate result is, my function doesn't autofilter

Interestingly, when I replaced the function with a Sub and passed the count in a Msgbox, it worked. So the problem I have is, the same code for autofilter which worked for Sub doesn't work for function. Any thoughts

 

[SydneyGeek]

Hi, and welome to the Board!

Functions in Excel don't manipulate worksheets; they only return values to the selected cell(s). That is why the Sub will filter but the Function will not.

Denis

 

[144725]

Thanks. Can I have a Sub that can autofilter the worklog sheet and have the funtion call the Sub?

 

[SydneyGeek]

Not as far as I know but I am open to correction on that.

If you want to count the number of projects with a set status (using multiple criteria) take a look at the SUMPRODUCT function. There are heaps of examples on the Board.

Denis

 

[mikerickson]

AutoFilter changes the environment of the worksheet.
If your function uses AutoFilter to get its result, may work when called by a VB routine, but will fail when called from a worksheet.

If you post your code and an explaination of what it does, a method of getting your result without AutoFilter might be found, that could be called from a spreadsheet.

 

[144725]

SUMPRODUCT function served the purpose. It works quicker than my VBA code.

I used the formula =SUMPRODUCT((WorkStream=$B3)*(Iteration=$C3)*(Analysis = "Completed"))

Thanks Denis