Populate the Excel Cells in the Calendar form from another sheet

[bijicha]

Hi Experts,

I need some help to create a macro to populate the calendar sheet in this file from the data of orders sheet.

I created the calendar sheet (in my one way, if you have another dynamic idea, please provide), in which the “Ord. No” to populate according to the “Loading date”. The Order no. cells in the calendar sheet to show in colors according to the “Load Code” in Orders sheet. It will be a maximum of 12 “Order no.” per day in Calendar. If “Loading Date” is blank, then not to update in the Calendar.

Thanks...

 

[Marc L]

As the calendar does not match the source colors & data so I can't help ‼​

You must attach a calendar without any error or if I'm wrong a better elaboration in order all be crystal clear.​

Explain the colors & the data for example for Feb 6th, Feb 17th, Feb 16th, …​

​

 

[bijicha]

Hi Marc,

My bad, corrected file. Please see this attachment . The colors indicating the "Load Code" in the orders sheet, the color legend in indicated in the Lookup sheet. I mean, if the "Order number" I filtered it for 18th Feb. for an example of the color indication based on the Load code.

thanks..

 

[Marc L]

Did you have corrected only the 18th feb ?! So you need a code only for the 18th feb.​

Even lurking to only the calendar 18th feb your expected data result still does not match the lookup sheet Dealer Code​

and why by hell the data is not in the same order than in Orders sheet ?! So again : no logic, no code …​

​

 

[bijicha]

Hi Marc,

Can you check the pictures.. and the attached file.... the original file is almost 36mb, so i just extracted to make the file handy.. Can you check the logic, which i showed in the below pictures (15th, 17th and 18th i captured) and if you can help me, please..

Thanks..

 

[Marc L]

So you misread : how can 6300635377 be the first result of 18th feb ?!​

And explain your 18th feb data result for 6300635266 …​

​

 

[bijicha]

Hi Marc,

I overlooked.. the data in the shared calendar sheet is not in sorted way, it was created manually adding each order number one by one. When in the macro, it can be in sorted order based on the order number. (Sorting is not mandatory, but all the order numbers should populate in respective date coulmn, with order number with three digit dealer code based on the load code color.

Let me know, if i need to share further more

thanks..

 

[Marc L]

And explain your 18th feb data result for 6300635266 …

 

[bijicha]

Hi Marc,

It should be 6300635266-PETR with Load color of LCLF. The load code data is not right, as i mentioned the calendar data was manually added.

Lookup sheet dealer and dealer code is only for sample for the purpose of coding. we are still working on the codes to generate accurately for each location.

Any way, i corrected the file manually... Please check and help

 

[Marc L]

According to your last attachment the logic is pretty almost respected so any helper can give a try to this beginner level task.​

But my controls tool returns there is some missing data within 14th feb so maybe you have forgotten to explain a rule ? …​

​

 

[bijicha]

Hi Marc,

Yes, its two entry missing on that day, updated..

Thanks..

 

[Marc L]

From an old smartphone ...​

​

As the logic is now fully respected so any helper can give it a try !​

​

As my tests laptop computer had a severe system crash this morning​

I can't log anymore to this site, other apps with strange behavior,​

fiber server was also down, looks like a virus or worse a serious malware.​

​

As I have to format the laptop hard drive, install Windows, apps​

so I could check your last attachment maybe tomorrow ...​

​

 

[bijicha]

Sure Marc, I am waiting for someone's help..

Thanks..

 

[p45cal]

Look at sheet Calendar (2) in the attached.
There are new formulae in rows 4,17,30,43 & 56.
Conditional formatting in cells B4:G67
No more than 12 orders in a day!
Hope your version of Excel is up to it.

 

[bijicha]

Hi P45cal,

Thank you very much. Its working perfectly and what i expected..

Keep this post as closed..

Thanks..

 

[Marc L]

It required more time to correct the Calendar worksheet than to write the code !​

Badly designed so bad formulas & conditional formatting removed and a week rows block is missing ‼​

​

To cover all possible cases, see new formulas in cell A1, rows #3, 55 & 68 …​

Weeks starting in rows #55 & 68 are by default hidden,​

revealed when necessary via new cells formatting like new conditional formatting.​

Sunday red days appear only if their next cells are not greyed / hidden …​

​

This is the Excel basics VBA procedure inside the Calendar worksheet module of the below attached revamped workbook :​

Private Sub IncludeOrders()
    Dim L&, R&(31), Rc As Range, D%, S$
        L = [A3].End(xlDown).Row - 4
        Application.ScreenUpdating = False
    With New Collection
        For R(0) = 3 To UsedRange.Rows.Count Step L + 1
            With Cells(R(0), 2)(2).Resize(L, 6):  .ClearContents:  .Interior.ColorIndex = xlNone:  End With
        For Each Rc In Cells(R(0), 2).Resize(, 6)
            If Rc Then .Add Rc(2).Address, CStr(Rc)
        Next Rc, R(0)
            For Each Rc In Feuil3.[A1].CurrentRegion.Columns(1).Cells:  .Add " - " & Rc(1, 2).Text, Rc.Text:    Next
            For Each Rc In Feuil3.[D1].CurrentRegion.Columns(1).Cells:  .Add Rc(1, 2).Interior.Color, Rc.Text:  Next
        For Each Rc In Feuil1.Range("A2", Feuil1.[A1].End(xlDown))
                 D = Feuil1.Evaluate("DAY(" & Rc(1, 4).Address & ")")
            If R(D) < L Then
                S = .Item(CStr(D))
                R(D) = R(D) + 1
                Range(S)(R(D)) = Rc & .Item(Rc(1, 2).Text)
                Range(S)(R(D)).Interior.Color = .Item(Rc(1, 3).Text)
            End If
        Next
    End With
        Application.ScreenUpdating = True
End Sub

Do you like it ? ► ► So thanks to click on bottom right Like ! ◄ ◄​

 

[bijicha]

Thanks Marc, was on journey, saw you update today only. I was okay with p45cal's solution. I will definitely check your suggestion as well ..

I truly agree, it was poorly created excel sheet and logic. Any way, the file is working as i requested for the support.

 

[Marc L]

As your original workbook is not complete with a missing rows block week …​

​

 

[bijicha]

Thanks Marc, its working and appreciated....