Half hour entry

[vmjan02]

I have a sheet as ARE Raw which has all the records in it. This has the skill number in a half hourly interval.

I have the list of records as Skill number in a column C. and in total there are 119 unique skill numbers.
I am not sure is there a code as it will take Max and Min time from the raw report ARE Raw sheet and will create a sheet which will have all the 119 unqiue skill number with half hourly interval a as 0:00 to 23:30 is the time.

ref the table below

SkillNum	Time
2100	0:00
2100	0:30
2100	1:00
2100	1:30
2100	2:00
2100	2:30
2101	0:00
2101	0:30
2101	1:00
2101	1:30
2101	2:00
2101	2:30
2101	3:00
2101	3:30
2102	0:00
2102	0:30
2102	1:00
2102	1:30
2102	2:00
2102	2:30
2102	3:00
2102	3:30
2103	0:00
2103	0:30
2103	1:00
2103	1:30
2103	2:00

 

[ranman256]

paste the code below into vba module,
it reads all skills from the SKILLS sheet,
then builds the grid of 1/2 hour for each.

run: CreateTimeSlots

Option Explicit
Public gcolSkills As Collection, gcolTime As Collection

'-------------------
Sub CreateTimeSlots()
LoadSkills
LoadTime
CreateTimeGrid

Set gcolSkillsh = Nothing
Set gcolTime = Nothing
End Sub


'-------------------
Private Sub LoadSkills()
Dim sSkill As String
  'get all the skills
Set gcolSkills = New Collection
Sheets("skills").Activate
Range("A2").Select
While ActiveCell.Value <> ""
   sSkill = ActiveCell.Value
   gcolSkills.Add sSkill, sSkill
  
   ActiveCell.Offset(1, 0).Select 'next row
Wend
End Sub

'-------------------
Private Sub CreateTimeGrid()
Dim s, t
Dim sSkill As String
Sheets("results").Activate
Cells.Clear
Range("A1").Value = "skill"
Range("b1").Value = "time"
Range("A2").Select
For s = 1 To gcolSkills.Count
   sSkill = gcolSkills(s)
   For t = 1 To gcolTime.Count
       ActiveCell.Offset(0, 0).Value = sSkill
       ActiveCell.Offset(0, 1).Value = gcolTime(t)
      
       ActiveCell.Offset(1, 0).Select 'next row
   Next
Next

End Sub

'-------------------
Private Sub LoadTime()
Dim vTime As Date, vLastTime As Date
vLastTime = "23:55"
vTime = "00:00"
Set gcolTime = New Collection
While vTime < vLastTime
   gcolTime.Add vTime
   vTime = DateAdd("n", 30, vTime)
Wend
'Beep
End Sub

 

[vmjan02]

I am getting error Compile Error

on this line

Set gcolSkillsh = Nothing