PDA

View Full Version : Solved: Diffrentiating same week numbers , but of different years.



sebas1102
05-28-2006, 11:25 PM
Hi all,
I have production schedule which states the materials required for production, and the production start date. The dates range from the past 6months to the future 6months ie dec 05 to nov 06.
I need to consolidate the materials needed according to the production week number.
The week numbers for the consolidation table straches from column 2 to column 54. The material list stretches from A1 to K1.
The consolidation seems alright until the same months of different years pops out.
Let's say i have a production start date at 3-jan-05 that requires materials AA, BB and CC.
And I have another production start date at 5th-jan-06 that requires materials AA, BB and CC also.

Although they are of different production dates, but vba cannot recognise it since they have the same week number when using weeknum(). I will eventually have inaccurate data, as I have added up the material quantities of diffrent production dates.

How do I solve this problem?

Currently, i am using WEEKISO function:

Function WEEKISO(Dat As Date) As Long
Dim lnDat As Long
lnDat = DateSerial(Year(Dat - Weekday(Dat - 1) + 4), 1, 3)
WEEKISO = Int((Dat - lnDat + Weekday(lnDat) + 5) / 7)
End Function

and For iRowStkdescWklyBrkDwn = 3 To LRowStkdescWklyBrkDwn
iItem = Cells(iRowStkdescWklyBrkDwn, StkDescWklyBrkDwn)
For iRowWkRangeWklyBrkDwn = 3 To LrowWkRangeWklyBrkDwn
iWeek = Cells(iRowWkRangeWklyBrkDwn, WkRangeWklyBrkDwn)
Windows("materialForecastNew.xls").Activate
Sheets("Sheet1").Activate
LRowMatlFCN = Cells(Rows.Count, StkDescMatlFC).End(xlUp).Row
Sum = 0
For iRowMatlFCN = 3 To LRowMatlFCN
If UCase(iItem) = Cells(iRowMatlFCN, StkDescMatlFC) And _
iWeek = Cells(iRowMatlFCN, WkNMatlFC) Then
Sum = Sum + Cells(iRowMatlFCN, lQtyMatlFC)
End If
Next iRowMatlFCN

mdmackillop
05-29-2006, 12:19 AM
I've not studied your code, but can you not add a year value to the week to ensure a non recurring value. 2006 would produce figures from 601 to 652, 2007 from 701 to 752 etc. Easy to strip off the years again, if the week values only are required.
Regards
MD

sebas1102
05-29-2006, 12:41 AM
oh! but how do I do that? do i change something from the weekiso formula?

mdmackillop
05-29-2006, 10:48 AM
The following will change WEEKISO to produce the amended result. I don't see how the WEEKISO value is used elsewhere, so be sure and check for unintended results.
Regards
MD
Function WEEKISO(Dat As Date) As Long
Dim lnDat As Long, Yr As Long
Yr = 100 * (Year(Dat) - 2000)
lnDat = DateSerial(Year(Dat - Weekday(Dat - 1) + 4), 1, 3)
WEEKISO = Yr + (Int((Dat - lnDat + Weekday(lnDat) + 5) / 7))
End Function

Shazam
05-29-2006, 06:05 PM
How about a formula?

=INT((A2-SUM(MOD(DATE(YEAR(A2-MOD(A2-2,7)+3),1,2),{1E+99,7})*{1,-1})+5)/7)

sebas1102
05-29-2006, 06:06 PM
The following will change WEEKISO to produce the amended result. I don't see how the WEEKISO value is used elsewhere, so be sure and check for unintended results.
Regards
MD


VBA:

Function WEEKISO(Dat As Date) As Long
Dim lnDat As Long, Yr As Long
Yr = 100 * (Year(Dat) - 2000)
lnDat = DateSerial(Year(Dat - Weekday(Dat - 1) + 4), 1, 3)
WEEKISO = Yr + (Int((Dat - lnDat + Weekday(lnDat) + 5) / 7))
End Function


omg thanks mdmackillop!!! you helped me cleared my all time headache!

oh, i used recording for the other part with weeknum bcos i didnt know how to use weekiso then, and forgot to change it back. thanks for pointing that out!
now its just a simple LRowProdnSDateMatlFC = Cells(2, ProdnSDateMatlFC).End(xlDown).Row
For irowmatlfc = 3 To LRowProdnSDateMatlFC
ddate = Cells(irowmatlfc, ProdnSDateMatlFC)
Cells(irowmatlfc, WkNMatlFC) = WEEKISO(ddate)
Next irowmatlfc
!