PDA

View Full Version : Help to calculate end date for each Dept VBA



VISHAL120
09-16-2011, 05:56 AM
Hi Gurus,

I needed help on that through VBA concerning the prod. flow. system.

Actually concerning the production flow i have an end knit date from which all end date for other dept is calculated BUT taking into consideration the Route type which gives the leadtime for each Dept.

Example:

End knit date : 20-06 Route type is 6 then its End knit + leadtime as per route type for each dept.

See Attached File for Better example. I have been struggling on that since weeks till now and i needed just a hint of how can i proceed with. Again it shall be with VBA not formula as this will be incorparated in another master file.

Many thanks in advance for the tips.

VISHAL120
09-19-2011, 07:01 AM
Hi All,

Again with the below i have been trying to use this code to transpose the route type to rows so that i can use vlookup later to calculate:

Sub RearrangeMe()
Dim cl As Range
Dim LR As Long
Dim LR2 As LongDim LC As Long

Sheets("Route_Type").Select
LR = Cells(Rows.Count, "A").End(xlUp).Row
'For Each cl In test.Range("A2:A" & LR)
For Each cl In Range("A5:A" & LR)
LC = Cells(cl.Row, Columns.Count).End(xlToLeft).Column
For i = 2 To LC
LR2 = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(cl.Row, "A").Copy Sheet2.Cells(LR2, "A")
Cells(1, i).Copy Sheet2.Cells(LR2, "B")
Cells(cl.Row, i).Copy Sheet2.Cells(LR2, "C")
Next i
Next cl
End Sub
And copy it to another. but its not doing it like it should be :

it shall place the data as such :
route dept leadtime
1 knit 2
1 mup 3
1 d.h 3

See the example on the attached file


Please find the attached file you run a test of the code to see and also its taking much more time to do.


Many thanks to help on that if its possible