Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to process rows in a selection
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to process rows in a selection
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to process rows in a selection
Hello Dave
Dave Peterson wrote in : 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, -- -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to process rows in a selection
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") FJ de Bruin wrote: Hello Dave Dave Peterson wrote in : 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
the process of displaying a subset of rows in a table means? | Excel Discussion (Misc queries) | |||
How can run a macro ( call a macro) on selection of any filtercriteria? | Excel Worksheet Functions | |||
Macro to automate process | Excel Discussion (Misc queries) | |||
Selection of Rows | Excel Programming | |||
Run a macro batch process | Excel Programming |