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

Funds received - many thanks! :cheers:

Hi Olaura - let me know if you need further assistance.

I sent you a private message with my paypal details.

Thanks,
Ger

Alright. No problem. By Eastern Time Zone, I wasnt sure if you meant Japan, eastern Europe or East coast of US 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

Hey...

Monday afternoon is relative to your physical location. what time zone are you in?

I don’t think this can be done with a formula to be honest. Might have to be a macro. Does that become an issue for you?

No need to validate the payment, I trust you.

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

Hi Jonny - this is the hire help forum and the rules of this forum state that you should indicate how much you are willing to pay for a solution in the thread title. How much are you offering and I can update the thread title. In addition, 10% should be paid to "[email protected]"[/email]

See Rules Here:

Ger

[ATTACH=JSON]{"data-align":"none","data-size":"custom","height":"122","title":"Smile-thumbs-up-clip-art-clipart-image-0.jpg","width":"100","data-attachmentid":1210921}[/ATTACH]

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?

Sub Fill_Formula()


Dim rng As Range
Dim Lastrow As Long


With Sheet1


    Set rng = .Range("B1:B" & .Range("A" & Rows.Count).End(xlUp).Row) 'create the range of cells to take the formula.
    
    With Application
        rng.Formula = "=Index(H1:H6, Match(A1, G1:G6, 0), 1)" 'insert the formula into the range of cells.
        rng = rng.Value 'convert the formula to an actual value (comment this line out if you want to keep the cell formula there)
    End With
End With


End Sub

Ger

<moved to Excel General>

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

You had two typo's

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

should be this

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

No formulas.... Use a Pivot Table, see attached.

[ATTACH]n1206999[/ATTACH]

= right(a2,2) & "-" & mid(a2,5,2) & "-" & left(a2,4)

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:

With OutMail


.To = "[email protected]"


.CC = ""


.BCC = ""


.Subject = "BLR FTEs Submission"


.htmlbody = strBody & RangetoHTML(rng)


'.strBody = Selection.Paste 'dont need this.


strlocation = "C:\Users\VVA0\Desktop\New folder\Raw Sheet.xlsm"


.attachments.add strlocation


.Display 'or use .Send


End With

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:

Sub Mail_Selection_Range_Outlook_Body()


Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim strBody As String




Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use a fixed range if you want
Set rng = Sheets("Summary").Range("A1:Z20").SpecialCells(xlCellTypeVisible)
On Error GoTo 0


If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If




With Application
.EnableEvents = False
.ScreenUpdating = False
End With


strBody = "<html><body>Hi Res,<br><br>Please find snapshot below for FTEs submitted. Please Decline by pressing control and <a href=""mailto:[email protected]?Subject=BLR%20FTEs%20has%20been%20declined,%20Please%20specify%20reason/s"" target=""_top"">clicking here</a><br><br>Please Approve by pressing control and <a href=""mailto:[email protected]?Subject=FTEs%20has%20been%20approved"" target=""_top"">clicking here</a><br><br>Thanks.</body></head>"






Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next


With OutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "BLR FTEs Submission"
.htmlbody = strBody & RangetoHTML(rng)
'.strBody = Selection.Paste 'dont need this.
.Display 'or use .Send
End With
On Error GoTo 0


With Application
.EnableEvents = True
.ScreenUpdating = True
End With


Set OutMail = Nothing
Set OutApp = Nothing


End Sub

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

'chip pearson: http://www.cpearson.com/excel/PlaySound.aspx
Public Declare PtrSafe Function sndPlaySound32 _
    Lib "winmm.dll" _
    Alias "sndPlaySoundA" ( _
        ByVal lpszSoundName As String, _
        ByVal uFlags As Long) As Long


Const SND_SYNC = &H0        ' (Default) Play the sound synchronously. Code execution
                            ' pauses until sound is complete.


Const SND_ASYNC = &H1       ' Play the sound asynchronously. Code execution
                            ' does not wait for sound to complete.


Const SND_NODEFAULT = &H2   ' If the specified sound is not found, do not play
                            ' the default sound (no sound is played).


Const SND_MEMORY = &H4      ' lpszSoundName is a memory file of the sound.
                            ' Not used in VBA/VB6.


Const SND_LOOP = &H8        ' Continue playing sound in a loop until the next
                            ' call to sndPlaySound.


Const SND_NOSTOP = &H10     ' Do not stop playing the current sound before playing
                            ' the specified sound.




Sub PlayTheSound(ByVal WhatSound As String, Optional Flags As Long = 0)
    If Dir(WhatSound, vbNormal) = "" Then
        ' WhatSound is not a file. Get the file named by
        ' WhatSound from the Windows\Media directory.
        WhatSound = Environ("SystemRoot") & "\Media\" & WhatSound
        If InStr(1, WhatSound, ".") = 0 Then
            ' if WhatSound does not have a .wav extension,
            ' add one.
            WhatSound = WhatSound & ".wav"
        End If
        If Dir(WhatSound, vbNormal) = vbNullString Then
            ' Can't find the file. Do a simple Beep.
            Beep
            Exit Sub
        End If
    Else
        ' WhatSound is a file. Use it.
    End If
    ' Finally, play the sound.
    sndPlaySound32 WhatSound, Flags
End Sub


Sub ListWavFiles()
    Dim N As Long
    Dim FSO As Object
    Dim FF As Object
    Dim F As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FF = FSO.GetFolder(Environ("SystemRoot") & "\Media")
    For Each F In FF.Files
        N = N + 1
        Cells(N, 1) = F.Name
        Cells(N, 2) = F.Path
    Next F
    ActiveSheet.Columns(1).AutoFit
End Sub




Sub PlayActiveCellSound()
    PlayTheSound ActiveCell.Text
End Sub

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:

Sub Macro2()
'
' Macro2 Macro
'


'
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:F500")   'adapt this range to suit your needs
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

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

Regards
Ger