Display a sequence of cell values based on the week

[Shu of AZ]

This question is also in General Questions with no response yet.

I have a block of cells with data; A1:L38. There are 169 sequences or types
of these blocks possible. Each individual block coorelates to a week from
Sunday to Monday. Ex. sequence id 43 coorelates to the week of Dec 1 at
00:01 in the morning which is Monday to Sunday the 7th at 11:59 pm. This is
not literally true as the first week I roll this out will actually be
sequence id 1 and so on. If I rolled this out today, next week would be
sequence 2.

So I have 169 weeks of different combinations of these cells contained
within A1:L38.

How can I auto fill a sheet ("Data") with the correct block for the week
that is current when you open the worksheet. Any ideas? Thanks in advance.

 

[Hong Quach]

Hi Shu,

This is one of the method that I think will solve your problem of getting
the correct sequence number that corresponds to a particular week.

'START_DATETIME is the date time of the 1st instance
'This must stay fix for the entire 169 weeks or more
'to produce the same sequence.
'Notice the week date of the choosen date will be use
'to calculate in the DateDiff().
'12-01-2008 00:00:00 is the beginning of Monday.
Const START_DATETIME = "12-01-2008 00:00:00"

Public Sub SetDataBlock()
Dim rg As Range
Dim CurrSeq As Integer

Set rg = Range("A1:L38")

'Find the sequence number
'(+1 is to shift away from 0 and Mod function)
CurrSeq = ((DateDiff("ww", START_DATETIME, Now())) Mod 169) + 1

'Fill the Data with the corresponding sequence
rg = CurrSeq
End Sub

Hong Quach