Hi guys, I've been programming for a while, but strangely never learned to use Pivot Tables. Recently I discovered how much work they can save me so I started using them. I'm still having trouble with the code though. Please take a look and tell me what I'm doing wrong:
Please Login or Register to view this content.
Last edited by GeorgY; 12-02-2009 at 02:16 PM.
Would probably help if you outlined exactly what was going on...
Seems as though you're trying to retrieve a value using the GETPIVOTDATA function based on a field stipulated in A2 - but based on your code you're clearing the sheet before doing anything... I would have expected the insertion of Forecast to fail on that basis.
the above shouldn't work based on the Cells.Clear statement but is pseudo-code showing how to update values etc without need for .Select etcPlease Login or Register to view this content.
Post back with a sample file if you can and/or additional info.
(that being said I confess I've never actually tried using the GETPIVOTDATA function from VBA... so not sure if the above would work or not...see: http://msdn.microsoft.com/en-us/libr...ffice.11).aspx)
Last edited by DonkeyOte; 12-02-2009 at 08:14 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I must have made a mistake pasting the code. Here is the full sub:
I tried writing it the way you have it, but still doesn't work. I get an "Application defined or object defined error."Please Login or Register to view this content.
Your syntax for GetPivotdata is still wrong. The first argument should be the name of the data field, the second the name of the column/row field and then a value from that field.
Also, FYI, if you want to refer to the range a pivot table occupies, you can just use its TableRange1 or TableRange2 properties.
Everyone who confuses correlation and causation ends up dead.
Can you help me out with this syntax. I think I understand what you are saying, but I am not sure.Originally Posted by romperstomper
Basically, I want to look up cell ("A2") in range "invPTrng"
It will be located in column "FLCCODE" and it will be under the "ATLANTIC" data field.
It should be:
if I understood correctly.Please Login or Register to view this content.
It doesn't work. Didn't you forget to put the data range in - "InvPT?"It should be:
if I understood correctly.Please Login or Register to view this content.
Just to make it simpler:
I am looking for the number: "20641" which is going to be located in range "invPT" in the first column A
When it's found, I want the get the number corresponding to it under the "ATLANTIC" data field (column B).
Does that make it clearer?
That's what that should do - you don't need the range since you are using the GetPivotData method of the pivottable. Perhaps you need text:
if that doesn't work, can you post a sample workbook?Please Login or Register to view this content.
It doesn't work. Please take a look at the WB that I've attached to my 1st post..
I would but there isn't one there...
I'm trying..the upload keeps failing
OK, it's there now. Just don't refresh the PT because I had to take out the source data to make it smaller.
OK. Which version of Excel are you using?
Excel 2000
The problem is that ATLANTIC is not the name of the data field - it's an item in the AREA column. The syntax you need is:
Please Login or Register to view this content.
Oh man, you have no idea how frustrated I was getting with this. Pivot tables and I don't get along, yet.
Thanks a lot!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks