Using 'lr as long' is a good idea, but the important question is how to determine the value of lr?
Originally Posted by sherbetdab
When I was writing this my first priority was getting the ligic working (and the logic too), and so yes I used hard coded values and now I need to make it a bit more flexible.
I'll do this, but for it to remain working there are a few conditions you need to adhere to when making changes to the worksheets.
This is the principal one:
- On the Rota sheet you have a row coloured black which is COMPLETELY BLANK (row 41). Keep this as a single blank row between the two tables! It doesn't, of course, have to be row 41.
Likewise below the second table,
- maintain a completely blank row below the last driver's name (currently row 73).
- A completely blank column in column J. (If you want to include more days/weeks on the Rota sheet, that's OK, just maintain at least 1 completely blank column after the last day.
- At the top of each table on the Rota sheet you have 2 header rows, keep them both as exactly 2 rows.
Why all this business about blank rows and columns? We'll be using the vba equivalent of selecting a single cell (try it yourself, select A1 on the Rota sheet) , then pressing F5 on the keyboard, choosing Special…, then choosing Current Region, OK. You'll see the selection change to include the whole top table. If there aren't completely blank rows and columns around the table this will fail and choose more cells. This will tell us:
- Where the bottom of that table is (could be your lr)
- Where the right of the table is
- That the top of the table below is 2 rows below that (impoortant, because we'll be doing the F5/Current Region thing from there to obtain the extents of the second table).
Assumptions made about the tables on the Rota sheet to allow smooth operation:
- That they both start in column A
- That the first day is in column D of both tables
- That the drivers nmames are in column A
- That the route numbers are in column B
Moving on to the Route Knowledge sheet.
- Drivers names are in column A, starting at row 2
- Route numbers are in row 1
- There is only one header row
- That you maintain a row of something below the last driver names (it doesn't have to be formulae as you have at the moment), and like wise you maintain an extra column of something at the right.
- Beyond those columns/rows you have at least 1 completely blank row/column (this is because we'll be doing the F5/Current Region thing and assuming the body of the table (the Ys) to be that result less one row at the top, less one row at the bottom and less one column at the right).
You can have more routes across the top, and more drivers names in column A in this Route Knowledge table than you have on the Rota sheet, but every route number and driver name on the Rota sheet has to be included (exactly the same) on the Route Knowledge sheet.
I'm going to take a break from this now until tomorow. FYI below is a list of lines that need changing and in red what needs changing (I just deleted lines that didn't need changing), also a few lines will need to be added.
For Each colm In .Range("D3:I40").Columns
ReportDriverShortage RouteDemandAndDriverAvailability, .Cells(42, colm.Column).Value, colm, MinRouteDriversAvailable, True, ""
With colm.Cells(Application.Match(RouteDemandAndDriverAvailability(k, 1), .Range("B3:B40"), 0))
With .Cells(44, colm.Column).Resize(29).Cells(Application.Match(DriverToUse, .Range("A44:A72"), 0))
If Len(msg) > 0 Then MsgBox "For " & .Cells(42, colm.Column).Value & ", the following routes have no driver because all available and qualified drivers have been allocated elsewhere:" & vbLf & vbLf & Mid(msg, 3) & vbLf & vbLf & "These will be marked 'No drivers left'", , "We've run out of drivers for the day!"
Sub ReportDriverShortage(RDA, myday, colm, mrda, AtStart, msg)
With colm.Cells(Application.Match(RDA(ii, 1), colm.Parent.Range("B3:B40"), 0))
Sub CreateNewRDDA(RDADA, SceRng, mrda)
Set myRng2 = .Cells(44, cll.Column).Resize(29).SpecialCells(xlCellTypeBlanks)
RouteColm = Sheets("Route Knowledge").Range("A1:AM1").Find(what:=RDADA(i, 1), LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False, searchformat:=False).Column
If Application.VLookup(DriverName, Sheets("Route Knowledge").Range("A2:AM30"), RouteColm, False) = "Y" Then