Comparing Weeks with today and selecting the right column

[MRxlicius]

Hi All,
I am trying to create a column that is basically, giving the variance of this week, to the previous week. I want to make this as automated as possible. An example is below: So in the table there are 5 columns with 5 dates, and I need in the 6^th column to have O2-N2, and I need this to move as the weeks are passing by, so next week will show O2-P2, and so on. I want this variance to change, using the TODAY function, so when you open the file, to change.

[TABLE="width: 702"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]11/09/2019[/TD]
[TD]18/09/2019[/TD]
[TD]25/09/2019[/TD]
[TD]2/10/2019[/TD]
[TD]9/10/2019[/TD]
[TD]VS previous WEEK[/TD]
[/TR]
</tbody>[/TABLE]
Can you help please?

 

[PCL]

I want this variance to change, using the TODAY function,

Can you explain how the date when you open the file may drive the formula to change ??
Is it in relation with the dates in the 5 columns ??

 

[MRxlicius]

So, basically, the date will change by using the function TODAY(). So the end result is always, current week vs past week. and the dates 11/09/2019 18/09/2019 25/09/2019 2/10/2019 will be changed manually every time there is an update needed, usually once every 3 months.

 

[SuzB17]

Hi,
will there be data in your columns 9below the dates) and is it this you're trying to find the variance in.. not the dates?

 

[PCL]

So the end result is always, current week vs past week.

In case the current week is not in the list of dates , like in your example we don't do anything , is it ?
The current week concerned dates starting at column "O" to column "R" !
For column "N" nothing to do !!

 

[PCL]

Perhaps next code could help

Option Explicit


Sub Treat()
Dim I As Integer, II As Integer, LR As Integer
Dim MyWk As Integer
    MyWk = Application.WorksheetFunction.WeekNum(Now(), 2)
    LR = Cells(Rows.Count, "N").End(3).Row
    For I = 1 To LR
        If (IsDate(Cells(I, "N"))) Then
            For II = 1 To 4
                If (Application.WorksheetFunction.WeekNum(Cells(I, "N")(1, 1 + II), 2) = MyWk) Then
                    Cells(I, "N")(1, 6) = Cells(I, "N")(1, 1 + II) - Cells(I, "N")(1, II)
                    Exit For
                End If
            Next II
        End If
    Next I
'
End Sub

 

[MRxlicius]

Hi,
will there be data in your columns 9below the dates) and is it this you're trying to find the variance in.. not the dates?

Hi, yes there is going to be data. For example, week 1 is 100 next week is 50, so the variance should show -50, next week is also 50, so variance is 0. But i want the change in the formula to happen as automated as possible.

 

[MRxlicius]

Perhaps next code could help

Option Explicit


Sub Treat()
Dim I As Integer, II As Integer, LR As Integer
Dim MyWk As Integer
    MyWk = Application.WorksheetFunction.WeekNum(Now(), 2)
    LR = Cells(Rows.Count, "N").End(3).Row
    For I = 1 To LR
        If (IsDate(Cells(I, "N"))) Then
            For II = 1 To 4
                If (Application.WorksheetFunction.WeekNum(Cells(I, "N")(1, 1 + II), 2) = MyWk) Then
                    Cells(I, "N")(1, 6) = Cells(I, "N")(1, 1 + II) - Cells(I, "N")(1, II)
                    Exit For
                End If
            Next II
        End If
    Next I
'
End Sub

Hi,
I will give that a try, however i was trying to avoid vba since if there is an issue with the code, i know people will not be able to debug it, if i cannot do it.