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
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