Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Debug on Pivot Table
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("A gings").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. -- Rob Gaffney |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Debug on Pivot Table
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 on error goto 0 End Functon "Gaffnr" wrote: 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("A gings").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. -- Rob Gaffney |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Debug on Pivot Table
Could you put
On Error Resume Next On Error GoTo 0 around the offending line. HTH, Barb Reinhardt "Gaffnr" wrote: 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("A gings").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. -- Rob Gaffney |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Debug on Pivot Table
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 -- Rob Gaffney "Patrick Molloy" wrote: 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 on error goto 0 End Functon "Gaffnr" wrote: 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("A gings").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. -- Rob Gaffney |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Debug on Pivot Table
Thanks Barb
What does on Error goto 0 mean? -- Rob Gaffney "Barb Reinhardt" wrote: Could you put On Error Resume Next On Error GoTo 0 around the offending line. HTH, Barb Reinhardt "Gaffnr" wrote: 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("A gings").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. -- Rob Gaffney |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Debug on Pivot Table
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 "Gaffnr" wrote: 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 -- Rob Gaffney "Patrick Molloy" wrote: 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 on error goto 0 End Functon "Gaffnr" wrote: 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("A gings").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. -- Rob Gaffney |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking to remove pivot fields from pivot table via macro | Excel Programming | |||
Macro on filtering pivot table (pivot fields) = debug | Excel Programming | |||
Code for a Macro that allows a Pivot table to be repeatedly run (fora dynamic rows in a table, but static field names) | Excel Programming | |||
Macro Debug | Excel Programming | |||
Steps from Macro recorder for Pivot table will not run as a Macro | Excel Programming |