allocating duties on works rota using VBA

I'm guessing you're doing something like (you'll have to qualify the ranges and or set them yourself since I haven't seen your code) for the top table and perhaps using the same formula for the lower table, however, you could alter the formula a bit for the lower table:
Now there shouldn't be a problem with blanks not being found in the lower table (unless all relief drivers for the day are resting - in which case, as I said before, come back).

In the SPARE column, you could have something specifying that that driver is NOT available, otherwise leave it blank, then use
above .value =.value for the lower table.
And by the way, this last suggestion would be a good way of taking a relief driver out of the equation if he's on semi-permanent duty - just put, say, Perm in the SPARE column against his name.

Near enough. I used.

With Range("E3:J40")
.Formula = "=IF(VLOOKUP($C3,'Rest Days'!$A:$H,C$201,FALSE)=0,$B3,""Rest"")"
.Value = .Value
End With

With Range("E44:J72")
.Formula = "=IF(VLOOKUP($C44,'Rest Days'!$A:$H,C$201,FALSE)=0,"""",""Rest"")"
.Value = .Value
End With

Although I hadn't put the .value = .value I copied and pasted the values, I've changed it to the above now.

I'm still getting the runtime error 91 when i hit the button.

The error is because it's not finding any blanks. You need to do your own debugging. When you get the error, click Debug, then in the Immediate pane type:
DrvrColm.select
and press Enter. Does it select the range you expect?
Are there any blanks at all in that range? - if not, why not? etc.
Provide the file and I can debug for you.

When i type DrvrColm.select i get the runtime error 91 Object Variable or with block variable not set

I've Managed to solve it p45cal.

Because B44 to B72 was empty the current region thingy wasn't picking up the entire range. I put one of the drivers down as sick.

I then changed the "" in the formula below to a cell reference (B44) this caused the formula for my range to return some zeros, I replaced the zeros with blanks and hey presto, when i hit the button the routes are allocated.

With Range("E44:J72")
.Formula = "=IF(VLOOKUP($C44,'Rest Days'!$A:$H,C$201,FALSE)=0,"""",""Rest"")"
.Value = .Value
End With
Thanks for your time and patience. eace:

Just put a header which always stays there, at the top of that column (row 43??). That should sort the current region operation. It shouldn't matter that the rest of the column is totally blank. I'm not currently near a machine so I can't be 100% certain that it's the case with your file.

I've done that, now if i want to put a relief driver on a route for the full week due to sickness or holidays i can enter the route number in column B, I have a worksheet change event that runs when i enter the week number in cell B1 which also updates the shift patterns, this will pick up the route number in column B for the relief driver. The only downside is that when i hit the button the route is still allocated to other drivers but i can live with that. I only have to delete the affected cells.

Thanks for your help, this has turned out way beyond my expectations.