macro auto compute start date and end date exclude sunday and holidays

Ruffy

New Member
Joined
Jun 23, 2018
Messages
1
Kindly need help.
I want auto compute the start date and end date in my data.
But when it generate it will include the sunday and holidays.


Code:
Sub Scheduling()Dim LastRow, LastCol, IMAN, IMAN_last, MINqty, day As Long
Dim qty_D, qty_W, qty_PO, NumDays, Capa_W As Long
Dim x As Long
Dim Ar_ProdPlan(0 To 1000) As Long ' bang luu production plan theo ngay
Dim Ar_Date(0 To 1000) As Date
Dim BlankStartDate As Boolean


'a = A_Date(2018, 18, 2)


MINqty = Sheet1.Cells(1, 1)


Sheet2.Activate
LastRow = Sheet2.Cells(1048576, 1).End(xlUp).Row


i = 2


Do
    'A. PO# dau tien cua IMAN
    Sheet2.Activate
    IMAN_first = i
    IMAN = Sheet2.Cells(i, 1)
        'PO# cuoi cua IMAN
    IMAN_last = Sheet2.Cells.Find(What:=IMAN, After:=Sheet2.Cells(i, 1), SearchDirection:=xlPrevious, SearchFormat:=False).Row
    
    
    'B. Check 1 vai dieu kien ko?
    
    'C. arrange POs
    'C1. Read production plan Table
    LastCol = Sheet1.Cells(3, 256).End(xlToLeft).Column
    Line = Sheet1.Cells.Find(What:=IMAN, After:=Sheet1.Cells(3, 1), SearchFormat:=False).Row
    Ar_ProdPlan(0) = 0
    NumDays = 0
    
    For j = 4 To LastCol
    
    If Sheet1.Cells(Line, j).Value > 0 Then
        Capa_W = Sheet1.Cells(Line, j).Value
        For thu = 2 To 7
            
            NumDays = NumDays + 1
            Ar_ProdPlan(NumDays) = WorksheetFunction.RoundUp(Capa_W / 6, 0)
            Ar_Date(NumDays) = A_Date(Sheet1.Cells(1, j).Value, Sheet1.Cells(2, j).Value, thu)
            
        
        Next thu
    
    End If
        
    Next j
    
    Ar_ProdPlan(0) = NumDays
    
    'C2. Run single PO
    
    capa = 0
    day = 0
    
    Do
        qty_PO = Sheet2.Cells(i, 5).Value
        'key start date
        If day < NumDays Then
            Sheet2.Cells(i, 9).Value = Ar_Date(day + 1)
        Else: Sheet2.Cells(i, 9).Value = "#"
        End If
                
        Do
            
            day = day + 1
            capa = capa + Ar_ProdPlan(day)
        
        Loop Until (day > NumDays) Or ((capa >= qty_PO) Or ((0 <= qty_PO - capa) And (qty_PO - capa <= MINqty)))
        
        If day > NumDays Then
        
            Sheet2.Cells(i, 10).Value = "#" & CStr(qty_PO - capa)
            i = IMAN_last + 1
        
        'ElseIf (0 <= qty_PO - capa) And (qty_PO - capa <= MINqty) Then
        ElseIf (Abs(qty_PO - capa) <= MINqty) Then
        
            '  end_date
        
            Sheet2.Cells(i, 10).Value = Ar_Date(day)
        
                
            ' start new PO
            i = i + 1
            capa = 0
        
        ElseIf qty_PO <= capa Then
            'end date
            Sheet2.Cells(i, 10).Value = Ar_Date(day)
            Ar_ProdPlan(day) = capa - qty_PO
            day = day - 1
            
            'start new PO
            i = i + 1
            capa = 0
        
        End If
        
        
    
    Loop Until i > IMAN_last
    
    i = IMAN_last + 1
    
Loop Until i > LastRow


Sheet2.Activate




End Sub
 
Last edited by a moderator:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Forum statistics

Threads
1,224,827
Messages
6,181,195
Members
453,021
Latest member
pingpong7117

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top