macro to process rows in a selection

[fdebruin]

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

 

[Dave Peterson]

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/Excel/Pivots/pivottables.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/assistance/2002/articles/xlconPT101.aspx

 

[FJ de Bruin]

Hello Dave

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.

Thanks for replying. I have been experimenting with those
pivottables and they do almost what I want. In fact, they are
so easy to use that I lowered my requirements a little bit and
gave up on the macro progamming.

Well, not completely as I still want to understand the
problem.

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

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

I thought that by doing:
Dim r1 As Range
Set r1 = Selection

r1 refers indeed to multiple cells. Because r1 is defined as a
range, the offset[i, j] should point to a single cell. My
assumption was backed by stuffing offset[i,j].value into a
different cell:
Cells(1, 1).Value = r1.Offset(row - 1, 2).Value

This worked for me, so I am still at loss why in some cases
offset[i, j] refers to a single cell and sometimes to multiple
cells.

Frank,

 

[Dave Peterson]

Try a couple of experiments

dim r1 as range
dim r2 as range
set r1 = range("a1:b2")
set r2 = r1.offset(1,2)
msgbox r2.address

And you'll see that the shape (number of rows/columns) didn't change.

You could resize it:

set r2 = r1.resize(1,1).offset(1,2)
or
set r2 = r1(1).offset(1,2)

And something else to watch out for:
You can have multiple areas in your selection.
Select and then ctrl-select manually or something like this in code:
Set r1 = Range("a1:b2,e1:f2")

Hello Dave

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.

Thanks for replying. I have been experimenting with those
pivottables and they do almost what I want. In fact, they are
so easy to use that I lowered my requirements a little bit and
gave up on the macro progamming.

Well, not completely as I still want to understand the
problem.

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

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

I thought that by doing:
Dim r1 As Range
Set r1 = Selection

r1 refers indeed to multiple cells. Because r1 is defined as a
range, the offset[i, j] should point to a single cell. My
assumption was backed by stuffing offset[i,j].value into a
different cell:
Cells(1, 1).Value = r1.Offset(row - 1, 2).Value

This worked for me, so I am still at loss why in some cases
offset[i, j] refers to a single cell and sometimes to multiple
cells.

Frank,