This portion could cause problems if you have multiple cells selected:

r1.Offset(row - 1, 2).Value

If the selection is a single cell, then this represents a single value.

If the selection is multiple cells, then this represents an array (with the same
dimensions as the selection--same number of rows and columns).

And since you're picking up the range from the worksheet, it consists of some
number of rows by at least one column (two dimensions).

And looking at the VBA's help for Filter, it says:
sourcearray Required. One-dimensional array of strings to be searched.

So you have a couple of problems.

========
Instead of using the approach you took, I'm gonna suggest looking at
Data|pivottable. You can do lots of summaries based on a your data.

There's a couple of worksheet functions that you could use to count the number
of entries, too:

If it's a simple count this if this is true:
=countif(c1:c10,"FDB")

But if it depends on multiple criteria:
=sumproduct(--(a1:a10="fdebruin"),--(c1:c10="FDB"))

But a pivottable could be the easiest way. If you've never used them, give
yourself an hour to play with it and you'll have a technique that saves you
hundreds of hours later.

If you want to learn more about pivottables, here are a few links.

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/...ivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx


fdebruin wrote:

I am trying to create an action item list manager. The list
itself is simple but now I want a macro to derive some
statistics, for example the number of action items that are
open per person.

Given my current list, the process is as follows:
a) filter on status to get all the open items (manual)
b) select the rows I am interested in (manual)
c) run a macro that goes through the rows checking and counting
the actionees (which are kept in a column). The macro reports
in a different worksheet.

Using the web and the help function, I have come up with the
follwing. All seems to work except for the test in line 13. I
cannot get that right. I guess it has something to do with the
data type of the .value method. I mostly get a data type mismatch
error.

Note that the actionee field can contain more than one person,
so I cannot use a direct test (which didn't work either).

Does someone have advise for me on how to get this working?
Does someone have a recommendation of a text book which deals
with this kind of macro programming?

1 Dim row As Integer
2 Dim r1 As Range
3
4
5 Dim count_fdb As Integer
6
7 Set r1 = Selection
8
9 Sheets("AI Summary").Select
10 Range("A1").Select
11
12 For row = 1 To r1.Rows.Count
13 If Filter(r1.Offset(row - 1, 2).Value, "FDB") Then
14 count_fdb = count_fdb + 1
15 End If
16 Next row
17 Cells(1, 1).Value = count_fdb

--
-- Frank

--

Dave Peterson