Hi All,

Thanks for the feedback so far.

Please see code below. I'm new to VBA so I'm sure there are
improvments to the code below. The code is taking a time input (time
window) and converting it to an amount of people available (put into a
table for reference). The code does now run faster, without the
changes you have mentioned so far as I removed it from a workbook
which was 22mb in size.

Sub OT_to_Availability_Grid(ByVal OTStartTime As String,
BookedStartTime As String)
'called from the Resource_OT routine (for each day of the week)

Dim DayStart, OTStart, MR1S, MR1E, OTEnd As Date
Dim DayStart_to_OTStart, OTStart_to_MR1S, MR1S_to_MR1E, MR1E_to_OTEnd,
OTStart_to_OTEnd As Date
Dim DayStart_to_OTStart_10m, OTStart_to_MR1S_10m, MR1S_to_MR1E_10m,
MR1E_to_OTEnd_10m, OTStart_to_OTEnd_10m As Integer

DayStart = 0.25

Dim Count, myOffset, TaskOffset, MROffset, StaffAmount As Integer
Dim Task As String

Dim c As Range
For Each c In Worksheets("Sched OT").Range(OTStartTime).Cells
If Not c.Value = "" Then ' Is the cell empty?
'c.Interior.ColorIndex = 3
'c.Offset(0, 1).Interior.ColorIndex = 3
'Else
OTStart = c.Value ' Get information required
OTEnd = c.Offset(0, 1).Value
MR1S = c.Offset(0, 2).Value
MR1E = c.Offset(0, 3).Value
Task = c.Offset(0, 4).Value

StaffAmount = 1
MROffset = 5

Select Case Task

Case Is = "Proc M"
TaskOffset = 0
Case Is = "Proc A"
TaskOffset = 1
Case Is = "MHE"
TaskOffset = 2
Case Is = "XD"
TaskOffset = 3
Case Is = "IND"
TaskOffset = 4

End Select

DayStart_to_OTStart = OTStart - DayStart 'Work out time between
duty elements
OTStart_to_MR1S = MR1S - OTStart 'Work out time between duty
elements
MR1S_to_MR1E = MR1E - MR1S 'Work out time between duty elements
MR1E_to_OTEnd = OTEnd - MR1E 'Work out time between duty elements
OTStart_to_OTEnd = OTEnd - OTStart 'used when there is no MR

DayStart_to_OTStart_10m = DayStart_to_OTStart / (1 / 144) 'Work
out 10 in blocks
OTStart_to_MR1S_10m = OTStart_to_MR1S / (1 / 144) 'Work out 10 in
blocks
MR1S_to_MR1E_10m = MR1S_to_MR1E / (1 / 144) 'Work out 10 in blocks
MR1E_to_OTEnd_10m = MR1E_to_OTEnd / (1 / 144) 'Work out 10 in
blocks
OTStart_to_OTEnd_10m = OTStart_to_OTEnd / (1 / 144) 'used when
there is no MR

'Round the 10min blocks to whole numbers

With Application.WorksheetFunction
DayStart_to_OTStart_10m = .Round(DayStart_to_OTStart_10m, 0)
OTStart_to_MR1S_10m = .Round(OTStart_to_MR1S_10m, 0)
MR1S_to_MR1E_10m = .Round(MR1S_to_MR1E_10m, 0)
MR1E_to_OTEnd_10m = .Round(MR1E_to_OTEnd_10m, 0)
OTStart_to_OTEnd_10m = .Round(OTStart_to_OTEnd_10m, 0)

End With

myOffset = 0
Count = 0

If Not MR1S = "" Then

Do 'Fill Duty start to MR1 start timeslots

Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_ to_OTStart_10m +
myOffset, TaskOffset).Value = _
Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_ to_OTStart_10m +
myOffset, TaskOffset).Value + StaffAmount
myOffset = myOffset + 1
Count = Count + 1
Loop While Count < OTStart_to_MR1S_10m

Count = 0
myOffset = 0

Do 'Fill MR1 start to MR1 end timeslots

Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_ to_OTStart_10m +
OTStart_to_MR1S_10m + myOffset, MROffset).Value = _
Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_ to_OTStart_10m +
OTStart_to_MR1S_10m + myOffset, MROffset).Value + StaffAmount
myOffset = myOffset + 1
Count = Count + 1
Loop While Count < MR1S_to_MR1E_10m

Count = 0
myOffset = 0

Do 'Fill MR1 end to OTEnd timeslots

Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_ to_OTStart_10m +
OTStart_to_MR1S_10m + MR1S_to_MR1E_10m + myOffset, TaskOffset).Value =
_
Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_ to_OTStart_10m +
OTStart_to_MR1S_10m + MR1S_to_MR1E_10m + myOffset, TaskOffset).Value +
StaffAmount
myOffset = myOffset + 1
Count = Count + 1
Loop While Count < MR1E_to_OTEnd_10m

Count = 0
myOffset = 0


Else


Do 'Fill Duty start to MR1 start timeslots

Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_ to_OTStart_10m +
myOffset, TaskOffset).Value = _
Worksheets("OT & Ag
Resource").Range(BookedStartTime).Offset(DayStart_ to_OTStart_10m +
myOffset, TaskOffset).Value + StaffAmount
myOffset = myOffset + 1
Count = Count + 1
Loop While Count < OTStart_to_OTEnd_10m
End If
End If
Next c

End Sub