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
"Shu of AZ" wrote:
> 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.
>
|
Similar Threads
