Option Explicit
Sub Inbound()
Dim nBnd As Double, x As Integer
Dim Rng As Range, CE1 As Range, Rw As Range, CE2 As Range
Dim RcrdSh As Worksheet, DtSh As Worksheet
Dim RcrdWb As Workbook, DtWb As Workbook
Dim NameA() As Variant 'There is no reason to adjust this piece any longer
'The following fills the NameA array of variant length based on how many etries you make
NameA() = Array("Baker, Bob", _
"Barnes, Kim", _
"Davies, Nick", _
"Bryant, Nick", _
"Davies, James", _
"Thomas, CJ", _
"Thompson, Reggie")
' in this case there are 7 entries into the array stipulated as 0 to 6
'Following determines where you will record the data
Set RcrdWb = Workbooks("Results.xlsx") 'adjust to workbook to which you are recording
Set RcrdSh = RcrdWb.Worksheets("Sheet1") 'likewise with workbook you are getting data from
'Following determines where you will get the data from
Set DtWb = Workbooks("data.xlsx") ' adjust to sheet name of the recording workbook
Set DtSh = DtWb.Worksheets("Sheet1") 'and sheet you will be getting it from
'The for loop goes through the array 0 to last entry in the Array NameA
For x = 0 To UBound(NameA) 'First name assigns to 0 so 0 to 1 less than count you have
'Find the first instance of the array that you are on at the end of a cell in column A
Set CE1 = DtSh.Range("A:A").Find("*" & NameA(x), After:=Cells(1, 1))
'If The name is found
If Not CE1 Is Nothing Then
'Find the next instance (after found name) where workgroup starts a cell in column A
Set Rw = DtSh.Range(CE1, DtSh.Cells(Rows.Count, "A").End(xlUp)).Find("Workgroup*", After:=CE1)
'if there is not an instance found of workgroup then set Rw = to the last row in column A
If Rw Is Nothing Then Set Rw = DtSh.Cells(Rows.Count, "A").End(xlUp)
'Set a range from the found name to next instance of workgroup
Set Rng = Range(CE1, Rw)
'use worksheetfunction sumif to find instances where inbound starts the cell in set Rng and sum column C of those rows
nBnd = Application.WorksheetFunction.SumIf(Rng, "inbound*", Rng.Offset(0, 2))
'Find the first available row in column A of Record sheet
Set CE2 = RcrdSh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'Assign name to column A of next available row
CE2 = NameA(x)
'One cell to right enter Inbound
CE2.Offset(0, 1) = "Inbound"
'One cell to right of Inbound entry enter the value for all inbound calls
CE2.Offset(0, 2) = nBnd
End If
Next
End Sub
