VBA to calculate differences between two items in a pivot table.

Hi, ive created a pivot table from my data.

From the table I want to build a formula that calculates differences between two columns (asset and liability).
I want just the formula in vba form, because I want to paste that formula after the last column of the pivot table in autofill mode.

The pivot changes in column number and row number so i cant really hard-code the formula to any cell or use the conventional method of using the pivot field settings.

Attached is a sample data. Need to calculate columns A minus B based on the Header "Name", not column and in VBA using Pivot table.

If you know the column name, like "asset" and "liability" you can run a code like below that keeps checking for "asset" and if it hits, it retrieves the column and row. Now that you know the asset and liabilities columns, you know which rows to add in your vba function:


for i = 0 to 50
if activecell.value = "asset"
variable = activecell.column
variable2 = activecell.row
else
activecell.offset(0,1).select
end if
next i

-----------------------------
http://refreshdata.net <-- we hope this isn't considered spam

Im sorry, I dont quite get what you mean, could you put that macro in action on my sample please?

A calculated field is a much better solution. I got too sidetracked with VBA.

Select a cell in your PivotTable
Press Alt+P to select PivotTable dropdown icon from Pivot Table toolbar
Select Formulas, and then Calculated Field.

JosephP and Refreshdata.net,

I cant, I tried doing it, it just crashes, what I did using vba is that i determined a column right after the last column in the pivot table, then made a vba that pasted formula along the dynamiccolumn (autofill) which in turn does what I want.

However I used the vlookup formula, which returns the value based on columns in the pivot table, however this is useless as vlookup returns value in specific columns, and since the columns change position when i import new data, the calculation is wrong.

Thats why i need a dynamic formula that calculates based on header of columns and calculates in sequential rows.

This is the layout


Pivot Table 1
A | B | C | D | DynamicColumn
x1 |y1 |s1 |r1 |x1-r1
x2 |y2 |s2 |r2 |x2-r2
x3 |y3 |s3 |r3 |x3-r3
x4 |y4 |s4 |r4 |x4-r4


As you can see I want the formula that does the calculation under DynamicColumn, seems easy until you cant use calculated items, and the header position changes frequently.

Generally I cant, the data is private n confidential, but Ill try look into it. I cant use the conventional method because the data is layered and complex, even the header names are dynamic, i am taking over a job from someone so if i tried to build it back from scratch will take months.

I did try the getpivottable method, but due to the field naming, i cant use it to autofill a range with that formula, Either that or I havent found a way to use that function.

Hello people, I solved this problem last week, forgot to close this thread.

Thanks Joseph,

I played around with the getpivotdata function like you suggested and finally got it working with my other macro's.

This was the solution if anyone cares, this was edited from someone else's function, so it maybe weird and confusing, but it works for me. Ask me if anyone needs to know more.

Please Login or Register  to view this content.