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