Doing math on cells in Pivot Table? 1

[willyboy58]

I have created my pivot tables to my liking, but now I need to do some math on the cells. I need to count the number of cells in a row that have any number of hours in them. In other words, cells that are not null. I should end up with a number around 20 (# of work days in a month). This count will be in a column to the right of the Pivot table. When I try to write the following code (abbreviated for this message) to do the count, I am told that “Cannot enter a formula for an item or field name of a Pivot Table.” I have tried to copy the table without the headings, but that doesn’t work either. So how do I do math on the cells of a Pivot Table? TIA Bill


'count the number of days worked and place total into cell
Sub CountingDays()

Dim DaysCounted As Single

Range("A4&quot.Select

While Not IsEmpty(ActiveCell)
With ActiveCell() 'start at A4

'following needed for each time loop goes around
.Offset(1, 0).Select
‘ MsgBox "should be at A5" use Msgbox to help me track 'flow of procedure

'code is shortened for this message. If I can get two of the cells to add, then I can get the rest
.Offset(0, 1).Formula = "=if(" & .Offset(0, 1).Address & ">=0,1," _
& .Offset(0, 1).Address & &quot"
.Offset(0, 2).Formula = "=if(" & .Offset(0, 2).Address & ">=0,1," _
& .Offset(0, 2).Address & &quot"

'the following is to sum the days and place the total in the last _
' column for each client
.Offset(0, 31).Formula = "=sum(" & .Offset(0, 1).Address & ":" _
& .Offset(0, 30).Address & &quot"
DaysCounted = .Offset(0, 31)

End With
Wend

End Sub

 

[DSMan]

What I understand your macro is trying to do is:

Start at A4

If A4 isn't empty then go into the loop

Step down one row (to A5)
Overwrite the contents of B5 with the formula " =if(B5>0,1,B5)"
Overwrite the contents of c5 with the formula " =if(c5>0,1,c5)"

(Continue doing this up to AE5, but this bit missed out of the listing for brevity)

then put the formula " = sum(b5:ae5)" into cell af5

then go back to the top of loop, but this time check to see if A5 is empty, and if it isn't, step down and .....

Presumably B5 to AE5 are in your pivot table, and Excel won't let you start overwriting pivot table cells with formulae. This is just as well, really, as your macro would overwrite your actual data with lots of circular references!

Also, if the bit in the middle was working, when you reach the last row of the table, and the value in column A isn't empty, the loop will continue one more time, stepping down into the next (empty) row and doing the process once more just for kicks.

Try this, instead:

Sub CountingDays()
dim DaysCounted as integer, ColOffset as integer

Range("A5&quot.select

while not isempty(Activecell)
with activecell()
DaysCounted = 0
for ColOffset = 1 to 30
if .offset(0,ColOffset)>0 then DaysCounted = DaysCounted+1
next ColOffset
.offset(0,31) = DaysCounted
.offset(1,0).select
end with
wend

end sub

I'm assuming here that Column AF isn't part of your pivot table. If it is, choose a column outside the table!

An even simpler way of getting what you want is to just type the formula ' = countif(B5:AE5,">0&quot ' into AF5 (or wherever) and copy it down - no macro required!