Macro Debug on Pivot Table

[Gaffnr]

Hi All,
I have created a macro that i have been using for some time to create a
pivot table.
The macro reads the data from a data tab and creates a pivot table in
another tab.
Ive never encountered this problem until today and its because i specify the
order of the column field and ive hit for the the first time when there are
no matches.
For example, I have a column in my data tab that contains an aging bucket
(0-30 days, 31-60 days, 61 - 90 days etc up to 365+ days).
Because when the pivot table is created my macro does not order these in the
correct sequence in the column area of the Pivot - i.e. it shows them as 0-30
days, then 120-150 days, then 365+ days then 31-60 days etc. I added a macro
statement to order these in the correct order, however when my macro reaches
the following statement, it bugs out:

'ActiveSheet.PivotTables("AgedUSD").PivotFields("Agings").PivotItems( _
"271 - 365 Days").Position = 8

The reason is that there are no items in the data tab that have an age
bucket of 271-365 days and thus it cant build a column.

Basically i need the statement to say if there are no matches skip, if not
order them as requested.
Please help - im totally stuck.
Rob
NB - i 'remmed out this statement and ran the macro which worked.

 

[Patrick Molloy]

simplistically, you could put an
ON ERROR RESUME NEXT
before these statements.
However, while efficient, its not good practice
ideally you sould pass the field name to a function that handles any error
and rturns true/false for example

If FieldExists("271 - 365 days") Then
..PivotFields("Agings").PivotItems( _
"271 - 365 Days").Position = 8
End If


your function would be
Functiob FieldExists(sfield as string) as boolean
on error resume next
dim pf as pivotitem
set pf = .PivotFields("Agings").PivotItems( _
"271 - 365 Days")
FieldExists = (err.Number=0)
err.clear

 

[Barb Reinhardt]

Could you put

On Error Resume Next

On Error GoTo 0

around the offending line.

HTH,
Barb Reinhardt

 

[Gaffnr]

Wow!! Im not an expert. Thanks so much for a fast and detailed reply.
I like the idea of doing it properly and using error handling but it is
above my skillset.
I dont think I understand what a function is
If you dont mind, to step thru your code.....

'I think im cleare with the logic of this although where is the field does
not exist? - should there be an else statement?
If FieldExists("271 - 365 days") Then
.PivotFields("Agings").PivotItems( _
"271 - 365 Days").Position = 8
End If

"Your function would be"
'should the below read Function and not Functiob - sorry, im truly not being
pedantic

Functiob FieldExists(sfield as string) as boolean
on error resume next
dim pf as pivotitem
set pf = .PivotFields("Agings").PivotItems( _
"271 - 365 Days")
FieldExists = (err.Number=0)
err.clear

'what does goto 0 mean? I want to carry on with the rest of the code.
on error goto 0
End Functon

 

[Gaffnr]

Thanks Barb

What does on Error goto 0 mean?

 

[Patrick Molloy]

ON ERROR
allows you to control how errors are handled

ON ERROR RESUME NEXT
is probably the simplest when you expect that there may be an error - such
as settign a field that doesn't exist

ON ERROR GOTO 0
turns off the previously set error handler

Usually my code has more robust error handlers, so for this kind of error, i
put it into a function.
A function is similar to any other subroutine, except it can also return
values

So this code

SUB Dummy()
a=10
b=0
on error resume next
v = a/b
if err.number <>0 then 'errored
err.clear
v=0
end if

End Sub

.... has lots of error handling


SUB Dummy2()

a = 10
b=0
v = myhandler(a,b)

End Sub
function myhandler(x,y)
on error resume next
myhandler = x/y
on error goto 0
End Function

by main sub, dummy2, is now much cleaner. Any errors I'd expect are now
handles in my function.

In your case, the line setting the position of t he item could cause an
error, so your choise is (1) put on error resume next / on error goto 0
around it or (2) create a function to handle it neatly
Obviosly (1) is easiest to implement

hope this was helpful. click YES