I'm trying to do a "count" function at the end of a dynamic pivot table


C

Corrie

It has been a long time since I wrote VB for a macro based tool. I'm trying to model the old language.

In the VB (macro)I added...

Dim destCell As Range
Dim NumOfCols As Long

Ran the pivot table and then...

With Worksheets("______")
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 0)
NumOfCols = .AutoFilter.Range.Columns.Count
End With
With destCell
.Offset(1, 0).Value = "Count Events"
End With
With destCell
.Offset(1, 2).Resize(1, NumOfCols - 2).FormulaR1C1 _
= "=SUBTOTAL(9,R5C:R[-3]C)"

Now I want to Count instead of Subtotal.

I don't know which row I will end up on with the pivoted data.
The "Count Events" works. The name of that row does show up in the first cell after the "Grand Total".

Now I want to count the column from the bottom starting from the first cell before the "Grand Total" to the beginning of the column. Any ideas?

When I record the macro I get: "=COUNT(R[-47]C:R[-1]C)" I don't want the count to begin in -47. I want it to start in the first 2 column of the last row offset by one and then fill the same formula for how any many columns of data there are.

Sorry. As you can see, I'm marginal at best in explaining what I'm trying to do...

VOL EE
DATE AS AC AT BK BM
2/9
2/11 1
2/12 -1
2/13 -2
2/14
2/15 -2 -50
2/18
2/19 -1
2/20 -5
2/21
2/22 -4
2/23 1
Total 1 -32 0 -51 -1
CntEvnt 1 5 0 2 1
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top