Ordering Calendar, VBA

[rohankekatpure1987]

Hi Team,

I'm struggling in developing an ordering system in excel. Can you please help:

Below is my data :
[TABLE="class: cms_table, width: 729"]
<tbody>[TR]
[TD="align: center"]Item[/TD]
[TD="align: center"]Order Closed[/TD]
[TD="align: center"]Sun-29[/TD]
[TD="align: center"]Mon-30[/TD]
[TD="align: center"]Tue-31[/TD]
[TD="align: center"]Wed-1[/TD]
[TD="align: center"]Thu-2[/TD]
[TD="align: center"]Fri-3[/TD]
[TD="align: center"]Sat-4[/TD]
[/TR]
[TR]
[TD="align: center"]Seafood[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]85.44[/TD]
[TD="align: center"]14.24[/TD]
[TD="align: center"]14.24[/TD]
[TD="align: center"]14.24[/TD]
[TD="align: center"]42.72[/TD]
[TD="align: center"]56.96[/TD]
[TD="align: center"]56.96[/TD]
[/TR]
[TR]
[TD="align: center"]Bakery[/TD]
[TD="align: center"]3,4[/TD]
[TD="align: center"]136.08[/TD]
[TD="align: center"]24.68[/TD]
[TD="align: center"]21.68[/TD]
[TD="align: center"]22.68[/TD]
[TD="align: center"]68.04[/TD]
[TD="align: center"]90.72[/TD]
[TD="align: center"]90.72[/TD]
[/TR]
[TR]
[TD="align: center"]Meat[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]123.48[/TD]
[TD="align: center"]20.58[/TD]
[TD="align: center"]20.58[/TD]
[TD="align: center"]20.58[/TD]
[TD="align: center"]61.74[/TD]
[TD="align: center"]82.32[/TD]
[TD="align: center"]82.32[/TD]
[/TR]
[TR]
[TD="align: center"]Produce[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]137.34[/TD]
[TD="align: center"]22.89[/TD]
[TD="align: center"]22.89[/TD]
[TD="align: center"]22.89[/TD]
[TD="align: center"]68.67[/TD]
[TD="align: center"]91.56[/TD]
[TD="align: center"]91.56[/TD]
[/TR]
[TR]
[TD="align: center"]Daily[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]129.78[/TD]
[TD="align: center"]21.63[/TD]
[TD="align: center"]21.63[/TD]
[TD="align: center"]21.63[/TD]
[TD="align: center"]64.89[/TD]
[TD="align: center"]86.52[/TD]
[TD="align: center"]86.52[/TD]
[/TR]
</tbody>[/TABLE]


Data Description:
A. Item indicates item description.
B. Order Closed : Day of the week we don't order
0: Order on all days; 1: No ordering on Sunday, 2: No ordering on Monday, 3: No ordering on Tuesday........
C. Sun-29, Mon-30, Tue 31: Quantities that needs to be ordered on a particular day.

Eg. For seafood, since order closed is zero, we need to order on all 7 days of the week. Sunday: 85.44; Tue: 14.24
For Bakery, since order closed is 3,4; We are not going to order on Tuesday and Wednesday and hence the above filled quantities 21.68, 22.68 are incorrect. The correct values will be 0 for both Tue and Wed and you will order 24.68 + 21.68 + 22.68=68.04 on Monday.

Similarly for Meat, Produce and Daily, the order quantities are incorrect as Order Closed column has values.

Can you please help me develop a VBA Code for correcting the incorrect values for all columns From Sun to Sat for all items.

Expected Output is :

Bold quantities are added with previous day order quantities.
[TABLE="class: cms_table, width: 729"]
<tbody>[TR]
[TD="align: center"]Description[/TD]
[TD="align: center"]Order Closed[/TD]
[TD="align: center"]Sun-29[/TD]
[TD="align: center"]Mon-30[/TD]
[TD="align: center"]Tue-31[/TD]
[TD="align: center"]Wed-1[/TD]
[TD="align: center"]Thu-2[/TD]
[TD="align: center"]Fri-3[/TD]
[TD="align: center"]Sat-4[/TD]
[/TR]
[TR]
[TD="align: center"]Seafood[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]85.44[/TD]
[TD="align: center"]14.24[/TD]
[TD="align: center"]14.24[/TD]
[TD="align: center"]14.24[/TD]
[TD="align: center"]42.72[/TD]
[TD="align: center"]56.96[/TD]
[TD="align: center"]56.96[/TD]
[/TR]
[TR]
[TD="align: center"]Bakery[/TD]
[TD="align: center"]3,4[/TD]
[TD="align: center"]136.08[/TD]
[TD="align: center"]69.04[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]68.04[/TD]
[TD="align: center"]90.72[/TD]
[TD="align: center"]90.72[/TD]
[/TR]
[TR]
[TD="align: center"]Meat[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]123.48[/TD]
[TD="align: center"]41.16[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]20.58[/TD]
[TD="align: center"]61.74[/TD]
[TD="align: center"]82.32[/TD]
[TD="align: center"]82.32[/TD]
[/TR]
[TR]
[TD="align: center"]Produce[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]137.34[/TD]
[TD="align: center"]45.78[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]22.89[/TD]
[TD="align: center"]68.67[/TD]
[TD="align: center"]91.56[/TD]
[TD="align: center"]91.56[/TD]
[/TR]
[TR]
[TD="align: center"]Daily[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]21.63[/TD]
[TD="align: center"]21.63[/TD]
[TD="align: center"]21.63[/TD]
[TD="align: center"]64.89[/TD]
[TD="align: center"]86.52[/TD]
[TD="align: center"]86.52[/TD]
[/TR]
</tbody>[/TABLE]

​

Can you help me in writing VBA Code where based on order closed, my order quantities will automatically get added to previous day.

Thanks in advance​

I'm again posting this question as the previous answer formula is not working for all scenarios.

 

[thraling]

Hi!

Does this code works for you?

Sub CorrectTable()    
    Dim wb As Workbook
    Dim ws As Worksheet


    Set wb = Application.ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")
    
    Dim days
    Dim c As Integer, x As Integer, OrDay As Integer
    
    For c = 2 To 6
        OrDay = 8
        days = Split(ws.Cells(c, 2).Value, ",")
        If (ws.Cells(c, 2).Value <> 0) Then
            For x = 0 To UBound(days)
                If (CInt(days(x)) - 1) < OrDay Then
                    OrDay = CInt(days(x)) - 1
                End If
            Next x
            For x = 0 To UBound(days)
                If (ws.Cells(c, CInt(days(x)) + 2).Value <> 0) Then
                    If (OrDay <> 0) Then
                        ws.Cells(c, OrDay + 2).Value = ws.Cells(c, OrDay + 2).Value + ws.Cells(c, CInt(days(x)) + 2).Value
                    End If
                    ws.Cells(c, CInt(days(x)) + 2).Value = 0
                End If
            Next x
        End If
    Next c
    
End Sub

NOTE: I'm assuming the Cell "Item" is in A1 of a sheet "Sheet1", and that you're always separating your days with a "," not other (or ", ")

 

[rohankekatpure1987]

Hi!

Does this code works for you?

Sub CorrectTable()    
    Dim wb As Workbook
    Dim ws As Worksheet


    Set wb = Application.ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")
    
    Dim days
    Dim c As Integer, x As Integer, OrDay As Integer
    
    For c = 2 To 6
        OrDay = 8
        days = Split(ws.Cells(c, 2).Value, ",")
        If (ws.Cells(c, 2).Value <> 0) Then
            For x = 0 To UBound(days)
                If (CInt(days(x)) - 1) < OrDay Then
                    OrDay = CInt(days(x)) - 1
                End If
            Next x
            For x = 0 To UBound(days)
                If (ws.Cells(c, CInt(days(x)) + 2).Value <> 0) Then
                    If (OrDay <> 0) Then
                        ws.Cells(c, OrDay + 2).Value = ws.Cells(c, OrDay + 2).Value + ws.Cells(c, CInt(days(x)) + 2).Value
                    End If
                    ws.Cells(c, CInt(days(x)) + 2).Value = 0
                End If
            Next x
        End If
    Next c
    
End Sub

NOTE: I'm assuming the Cell "Item" is in A1 of a sheet "Sheet1", and that you're always separating your days with a "," not other (or ", ")

Thanks for your quick reply. It's working in the above scenario. I'm still trying to understand the logic of the code.

Thanks a bunch Sir!!!

 

[thraling]

Happy to hear that.

OrDay is the day you have to place the order. It's not just "the day before", since you could have 2 consecutive days off (like in "bakery"), and then the second day off would be the previous day that was also off. So I take the smallest (first "For"). I notice now that this logic can have some problems in case of alternate days of opening and closure, in which case you would need an array of "OrDay"

"days" is an array of the days you're off. It's the result of the split(), that takes a string as an input, divide it by "," and returns an array (days, indeed) of substrings. Substring, notice that, not integers. So, if you want to use that substring [tipically day(x)] as an integer you have to convert it. That's why CInt(day(x)): because day(x) is "3" (for example) as a string, not as a number.

Then, the second "For" takes all the days present in the column B and, if it is not a 0 (first "If", 0 means we order all the week long) sum the value present in the cell identified as "closed" (days(x)) to the cell identified as day of order (OrDay). This in case OrDay is different from 0 (you don't have a day of the week "0", the minimum is 1, so you don't place the order at all). Then, set to 0 all the "closed days". I had to put +2 a little bit everywhere because the first day of the week (1, Sunday) was on column 3, so all the table is shifted.