Originally Posted by
Svmaxcel
Fantastic work!!!!!
Had some questions here.
1) should we use Vlookup or Index/Match, people usually say that Index/Match is much more faster.
2) Iferror is not used in any formula, in case there Is an error(values not found), what will happen.
3) Can we create a new Sheet after the execution is done with the format attached.
1 - People say that. VLookup is simpler
2 - This fills in a "-" just in case, and the On Error Resume Next says to ignore any error, which leaves the "-"
For i = 12 To 28
.Cells(i).Value = "-"
Next I
On Error Resume Next
3 - Yes. See 'Roster' in the attachment
I don't like having to VLookup() the WB and WE values since they can be computed from the Date -- what are the rules to determine WB and WE?
Then you wouldn't need Mapping I:N at all
Option Explicit
' 1 2 3 4 5 6 7 8 9 10 11
'Skill Group Date ID Calls Avg Handle Time In Avg Talk Time In Avg Hold Time In Avg Wrap Time In Not Ready Time (Per Agent) Logged On Time (Per Agent) Available Time (Per Agent)
' 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
'Day Month Week Week Beginning Week Ending Agent Name Team Manager Location SkillSet Language Total Handle Time Total Talk Time Total Hold Time Total Wrap Time Not Ready Time(min) Logged On Time(min) Available Time(min)
Sub AddData()
Dim rData As Range, rRow As Range, rDates As Range, rNames As Range, rSkills As Range, rRoster As Range, rRoster1 As Range
Dim i As Long
Dim oWSF As WorksheetFunction
If Not TypeOf Selection Is Range Then Exit Sub
Set rData = Worksheets("Data").Range("A1").CurrentRegion
Set rDates = Worksheets("Mapping").Range("I1").CurrentRegion
Set rNames = Worksheets("Mapping").Range("A1").CurrentRegion
Set rNames = rNames.Cells(1, 2).Resize(rNames.Rows.Count, rNames.Columns.Count - 1) ' emp number is in A
Set rSkills = Worksheets("Mapping").Range("P1").CurrentRegion
Set oWSF = Application.WorksheetFunction
Application.ScreenUpdating = False
For Each rRow In Intersect(Selection.EntireRow, rData).Rows
With rRow
If .Row = 1 Then GoTo GetNext
If Len(.Cells(1).Value) = 0 Then GoTo GetNext
For i = 12 To 28
.Cells(i).Value = "-"
Next i
On Error Resume Next
' 1 2 3 4 5 6
'Date Day Month Week Week Beginning Week Ending
.Cells(12).Value = Format(.Cells(2).Value, "DDDD")
.Cells(13).Value = Format(.Cells(2).Value, "MMM-YY")
.Cells(14).Value = "WK" & oWSF.WeekNum(.Cells(2).Value)
.Cells(15).Value = oWSF.VLookup(CLng(.Cells(2).Value), rDates, 5, False)
.Cells(16).Value = oWSF.VLookup(CLng(.Cells(2).Value), rDates, 6, False)
' 1 2 3 4 5 6
'EMP ID ID Name Designation Supervisor Location Team Name
.Cells(17).Value = oWSF.VLookup(.Cells(3).Value, rNames, 2, False)
.Cells(18).Value = oWSF.VLookup(.Cells(3).Value, rNames, 4, False)
.Cells(19).Value = oWSF.VLookup(.Cells(3).Value, rNames, 5, False)
' 1 2 3 4 5
'Skill_Name Department Product Skill_LOB Skill_Language
.Cells(20).Value = oWSF.VLookup(.Cells(1).Value, rSkills, 4, False)
.Cells(21).Value = oWSF.VLookup(.Cells(1).Value, rSkills, 5, False)
If .Cells(5).Value <> 0 Then .Cells(22).Value = .Cells(4).Value * .Cells(5).Value
If .Cells(6).Value <> 0 Then .Cells(23).Value = .Cells(4).Value * .Cells(6).Value
If .Cells(7).Value <> 0 Then .Cells(24).Value = .Cells(4).Value * .Cells(7).Value
If .Cells(8).Value <> 0 Then .Cells(25).Value = .Cells(4).Value * .Cells(8).Value
.Cells(26).Value = .Cells(9).Value / 600
.Cells(27).Value = .Cells(10).Value / 60
.Cells(28).Value = .Cells(11).Value / 60
On Error GoTo 0
End With
GetNext:
Next
'make roster worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Roster").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Worksheets.Add.Name = "Roster"
With rData
.Columns(2).Copy Worksheets("Roster").Range("A1")
.Columns(3).Copy Worksheets("Roster").Range("B1")
.Columns(17).Copy Worksheets("Roster").Range("C1")
.Columns(18).Copy Worksheets("Roster").Range("D1")
End With
Set rRoster = Worksheets("Roster").Range("A1").CurrentRegion
With rRoster
.EntireColumn.AutoFit
Set rRoster1 = .Cells(2, 1).Resize(.Rows.Count - 1, .Columns.Count)
End With
With Worksheets("Roster").Sort
.SortFields.Clear
.SortFields.Add Key:=rRoster1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=rRoster1.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange rRoster
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
End Sub