Comparing Weeks with today and selecting the right column

MRxlicius

New Member
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 6th 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.

NOPQRS
11/09/201918/09/201925/09/20192/10/20199/10/2019VS previous WEEK

<colgroup><col span="6"></colgroup><tbody>
</tbody>
Can you help please?
 

PCL

Well-known Member
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 ??
 
Last edited:

MRxlicius

New Member
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.
 
Last edited:

SuzB17

New Member
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

Well-known Member
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 !!
 
Last edited:

PCL

Well-known Member
Perhaps next code could help
Code:
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

New Member
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

New Member
Perhaps next code could help
Code:
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.
 

Some videos you may like

This Week's Hot Topics

Top