Formula with rolling columns

[Dan4321]

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

 

[Fishboy]

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

Hi Dan, welcome to the boards.

If I am understanding you correctly, does this do as you describe?

Sub TEST()
LastCol = Sheets("Sheet2").Cells(7, Columns.Count).End(xlToLeft).Column
Sheets("Sheet1").Range("B7").Formula = "=(" & Sheets("Sheet2").Cells(7, LastCol - 1) & "-" & Sheets("Sheet2").Cells(7, LastCol) & ")/" & Sheets("Sheet2").Cells(7, LastCol)
End Sub

 

[Dan4321]

Perfect ! exactly what I was hoping for ! thanks !

 

[Fishboy]

Perfect ! exactly what I was hoping for ! thanks !

Happy to help, thanks for the positive feedback.