Posts by Ger Plante

    Firstly, after you added the Gantt chart it effected the headers a little bit, so your driver job allocations were in the wrong place. I've updated that now (attached).


    Secondly, I'm not sure I "See" the problem, I can see that the Gantt is in 5 minute intervals and this looks correct for the drivers. Can you show me a specific example of where "it didn't assign 2 jobs because it started 3 minutes before the driver available finished their previous job".


    Thx.
    Ger

    Alright. No problem. By Eastern Time Zone, I wasnt sure if you meant Japan, eastern Europe or East coast of US :thumbup::thumbup: but it doesn't matter, I think I have it completed.


    Please see attached and run the macro "Do_Allocations" and let me know if this is what you wanted/needed.


    The VBA code may need to be tweaked a little if your "actual" data is in different rows or columns, but I commented the code as best as I could so you can either try to update it yourself or let me know and I can update it for you, but certainly for your sample data it seems to match your expected results.


    I dont think this can be done with formulas - or certainly not very straight forward anyway.


    Regards,
    Ger

    Hi Olaura,


    4 Quick questions...


    1. in your example, on row number 11, you gave that job back to driver number 1. Why not give it to driver number 9, who had a start time at 5.25, and was unallocated at that time. Similarly I suppose for drivers, 10, 11 & 12 - why didnt they get any job when they seemed to be available.
    2. Is a vba solution OK (Macros)?
    3. When do you need it by?
    4. Did you pay 10% to ozgrid as per the rules? (I personally dont have visibility of payments).


    Thanks,
    Gerard

    Hard to see what you are doing, because your sample table of data didnt copy/paste (misalignment) very well


    Is this what you are trying to do?


    Ger

    Hi Haygunnohm, I created a new thread for you - there should be a "New" Button on each forum.


    You had two typo's

    Code
    1. Range("A4").AutoFill Range("A4:A" & Range("D" Rows.Count).End(x1Up).Row)


    should be this

    Code
    1. Range("A4").AutoFill Range("A4:A" & Range("D" & Rows.Count).End(xlUp).Row)


    The second typo was in "xlup" - XLUP, you wrote X1UP
    Edit: But I see pike has already answered :)

    Without seeing your current code, its impossible to say, and also not sure what you mean by "not picking it up"... is it a compile error, or just not doing what you expected...? Anyway, the code you need is using a method called .attachments.add


    See Ron's site -
    https://www.rondebruin.nl/win/s1/outlook/amail6.htm


    Using the last piece of code I Pasted above


    It would probably look like this:



    THIS IS UNTESTED CODE.


    But take a read of Ron's page above to see how to add the attachments based on the file name being stored in a cell.


    Ger

    Forget about "generateemail" Sub. You needed to wrap your strBody string in properly formatted HTML code... You then append that HTML string to the RangetoHTML conversion as part of .HTMLbody mehtod.... here is the code:



    Ger

    Cool. Sounds like you know what youre doing and you may just have hit the natural limit of Excel. I think the key thing is that - as you said - it corrupts while saving. Excel just "doesnt do" large files.


    I have personally noticed that when saving moderately sized files (say 10MBs) Excel can crash if there a lot of formula calculations going on, especially array formulas, on not many rows either. So maybe considering turning off calculations or preventing calculations while saving.


    https://ccm.net/faq/11254-exce…te-workbook-before-saving


    So what I'm saying is, you could pay someone a million dollars and the result will be no different. Fixing that would require a fix from MS to fix the Excel application itself.


    I'm genuinely struggling though to understand how a well formed and pruned Excel file could end up being so large. The solution on your side of the fence is to make your workbook as small as possible (MB's) with as few calculations as possible - and you believe you have done this. The solution on the Excel side is for MS to fix the less than stable saving of large excel files.


    I guess you've read about Excel's file size limitations... (from MS themselves). Seems like you are skirting very close to these numbers...


    https://support.office.com/en-…e8-45a8-9be2-b58778fd68ef


    Cheers,
    Ger

    Hi Jason - In the nicest possible way, that doesnt sound like a solution :) More like a hacky workaround, but thanks for sharing.


    In situations like this, where I suspect the excel workbook is corrupted and crashing regularly, I rebuild the whole thing. Even in complex applications. It only takes an hour or two.
    1. Export forms, classes, vba etc from within the editor
    2. Copy the data Only into new worksheets in another workbook. When I say data only, I dont mean copy the worksheet, I mean select only the data and copy that.
    3. Start rebuilding.


    It feels daunting at the start - but the thought of doing is often the worst part.


    By the way, have you used a process of elimination to find out why the workbooks are so big?


    Also, have considered putting your data in MySQL, or Access? Again, seems daunting, but if your tables are well structured, it shouldnt take that long.


    Finally.... Syncing a 1 GB file to Onedrive sends shivers up my spine. Worst software product ever. It may not be your problem, but Onedrive is fraught with problems.


    Ger

    Yes, it works on Windows 7 - I have 64 bit, so I needed to change the declare statement slightly:


    http://www.cpearson.com/excel/PlaySound.aspx



    its because the numbers have different formatting types in their source table.


    Go the chart on Page 2.
    Right click, and click on Edit Data
    Select all the data in Column B (Range B2:B7)
    Right click on selection
    Select "Format Cells"
    Select Custom from Left Category
    Select Type and Enter the value of:
    [h]:mm:ss


    Click OK


    Y Axis on the left should show up now.


    Ger

    Recording a Macro shows this:


    You just need to adjust the sheet names and ranges to suit your needs!


    Regards
    Ger