calculation of variation of data of rolling columns

[Dan3456]

Hi Guys!
Hope to find you well!

I got a problem with a VBA code.

here the problem:
I get 2 sheets: sheet1 and sheet2.

Sheet2 changes every day, fulling in one more column each day that passes. for example Day one there will be 2 column filled with data, day 2, 3 columns and so on.

I was wondering how i can write a formula in VBA on sheet1 (let's say in B7) for calculating the variation in %(e.g. expressed (N7-M7)/M7 ) of the last two columns in sheet2.

Ideally this formula will calculate the following
The first time, the formula will calculate (N7-M7)/M7, being N7 and M7 the last 2 column "not empty" in sheet2. The second time the formula will calculate (O7-N7)/N7, being O7 and N7 the last columns "not empty" in sheet 2. And so on.

Help would be amazing !
thanks !
Dan

[mrice]

Try

=(INDIRECT("Sheet2!R"&CELL("row")&"C"&COUNTA(Sheet2!7:7),FALSE)-INDIRECT("Sheet2!R"&CELL("row")&"C"&COUNTA(Sheet2!7:7)-1,FALSE))/(INDIRECT("Sheet2!R"&CELL("row")&"C"&COUNTA(Sheet2!7:7),FALSE))

in B7.

I have assumed that columns to the left of the last column have values in them as I am counting them to work out where the last column is.