Macro Debug on Pivot Table

  • Thread starter Thread starter Gaffnr
  • Start date Start date
G

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.
 
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
 
Could you put

On Error Resume Next

On Error GoTo 0

around the offending line.

HTH,
Barb Reinhardt
 
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
 
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
 
Back
Top