On Error Resume Next

[Guest]

Hello everybody!

I was wondering if someone could help me with the following. I wrote a macro
which get's information with matches the criteria k from a Pivot Table. I've
named K allready. Though it only returns "(Blank)" the criteria I stated in
case K was not found. I tested it with criterias which were sure to have
results but did not get any reslults other than (blank) either.

I think it has got something to do with the "On Error Resume Next" I used
but cant figure out what.

Application.CommandBars("PivotTable").Visible = False
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").PivotFields("ARF Code").CurrentPage =
"" & k & ""
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").PivotFields("ARF Code").CurrentPage =
"(blank)"
On Error GoTo 0
Application.CommandBars("PivotTable").Visible = False


Thanks in Advance!!

 

[Nick]

I assume you have assigned the name of the page to the variable K.
Your code is putting " around the variable name try removing those and just
using the K variable.
Also make sure that the K variable matches the format of the Page value in
the pivot table, i.e. 2 <> 02.

Nick

 

[Nick]

One other point. If this is the exact code from your procedure then you will
always get <blank> as that is the last statement.
So the code will show page K then <blank> but unless you have a pause in
between you wont see it.

If you are trying to trap the error so that if the age K does not exist and
you want to show <blank> then you need to build these statements in to a
error trapping routine or test the err.number in Select Case or an If
statement.

Nick

 

[Guest]

To bad, I tried both options but none fixed the bug. Could you tell me how to
use the "On error Resume Next" function? I think I made a mistake in that
line.

So if I want the Macro to fill in the Value stated under K

ActiveSheet.PivotTables("PivotTable3").PivotFields("ARF Code").CurrentPage =
"" & k & ""

But if it does not find K It will fill in (Blank)

ActiveSheet.PivotTables("PivotTable3").PivotFields("ARF Code").CurrentPage =
"(Blank)"

What macro function should I use then? Thanks in advance!

 

[Nick]

In order to throw an error you first need to set the CurrentPage to "(All)"
Then set the page you want using the variable K
If you don't set the page to all and the value of K is not found then the
current page will be renamed to the value in K.
So if you CurrentPage value is showing the items for say "A" and then you
set K to "Z" but "Z" does not exist in the pivot table the current page will
be renamed from "A" to "Z" and no error will be thrown.


example code could be


ActiveSheet.PivotTables("PivotTable3").PivotFields("ARF Code").CurrentPage =
"(All)"
On Error Resume Next
ActiveSheet.PivotTables("PivotTable3").PivotFields("ARF Code").CurrentPage =
k

If Err.Number=1004 then ' The error thrown when no value is available
ActiveSheet.PivotTables("PivotTable3").PivotFields("ARF
Code").CurrentPage = "(All)"
End If

On Error Goto 0 ' Turn off error handler

Hope this helps.

Nick