PC Review


Reply
Thread Tools Rate Thread

CalculatedFields.add function not working when refreshed...

 
 
=?Utf-8?B?RGVlZ2Fu?=
Guest
Posts: n/a
 
      1st Mar 2007
I am updating a pivot table based on the users input from a drop down box.

The rows will become one of four different options based on that input, but
I'd also like to add a RANK to their return - which is a running total
calculated field.

When I run the calculated field the first time it works fine, however, when
I refresh, it seems to want to add it again and it gives me an error. It
won't let me reference the calculated field like it does the real fields
either.

I'm stuck, can ya' help?

J

Public Sub changepivot()

Dim P As PivotTable
Set P = Worksheets("Capital").PivotTables("FakePivot")
Set PRegion = P.PivotFields("Region")
Set PState = P.PivotFields("State")
Set PTotal1 = P.PivotFields("Total1")
Set Prank = P.CalculatedFields("Rank")

If Range("Selected").Value = "All" Then
With P
PRegion.Orientation = xlHidden
PState.Orientation = xlHidden
End With
ElseIf Range("Selected").Value = "Gulf" Or Range("Selected").Value =
"Atlantic" Or Range("Selected").Value = "North" Or Range("Selected").Value =
"Florida" Then
With P
With PRegion
.Orientation = xlRowField
.AutoSort xlDescending, "Sum of Total1"
.Position = 1
End With
PState.Orientation = xlHidden
.CalculatedFields.Add "Rankb", "=1" 'THIS IS WHERE IT ERRORS
End With
Else
With P
With PState
.Orientation = xlRowField
.AutoSort xlDescending, "Sum of Total1"
.Position = 1
End With
PRegion.Orientation = xlHidden
End With
End If

End Sub

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Continuous form: Query to eliminate flash and refresh on calculatedfields. Greg (codepug@gmail.com) Microsoft Access Queries 0 31st Jan 2009 02:29 AM
Newly created Get Function is not working when I copied the syntax from a working function CJ Microsoft Excel Programming 1 16th Jan 2007 05:28 AM
calculatedfields =?Utf-8?B?R2VvZmY=?= Microsoft Excel Programming 2 24th Jun 2005 04:36 PM
CalculatedFields in Pivots Geoff Microsoft Excel Programming 0 5th Oct 2003 11:03 AM
Date/Time data source refreshed function Grant Microsoft Excel Programming 1 22nd Jul 2003 11:52 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:45 PM.