In VBA, change Excel Formula by incorporating columns in calculation

[KDP36]

Hi all,

Currently I have a formula in a sheet that calculates variance from a row in a Pivot table, such as B20 =VAR(B5:BL5). As additional data gets loaded into a data tab where the Pivot table reads in from (automatically refreshed by using ThisWorkbook.RefreshAll), how do I increment the number of columns each time to add new cells into this variance formula. For example, my current logic is L2 = 'NewDate', then B20 =VAR(B5:BM5). As NewDate gets updated, the last column in the variance function in B20 will move from BL to BM, BN, ... etc.
How do I do this? I tried to write a loop to increment the count but failed. Please advise on what's the most efficient way to do this, could be either in VBA or else. Much appreciated!

Stan

 

[Marcelo Branco]

Hi Stan

Provided there are not data at the right of your values, since the function VAR ignores blank cells maybe you can simply use, say, =VAR(B5:ZZ5).

Hope this helps

M.

 

[KDP36]

Thanks Marcelo. Unfortunately there is since this is a Pivot table. The rightmost column is the aggregated value, hence I don't want to include that.

 

[Marcelo Branco]

I suppose the rightmost column has a header in row 4, say, Grand Total or whatever. So maybe this works
=VAR(B5:INDEX(5:5,MATCH("Grand Total",4:4,0)-1))

M.