The question is exactly as described in the header, but I will give an overview of what the code is intended to do for better context as it is a chunky bit of code.

1. Iterate through 6 tables in worksheet A

2. Iterate through each employee name in each of those tables in worksheet A

3. Compare each employee name to each employee name in worksheet B

4. If a match is found, retrieve the condition that the employee completed (within worksheet B)

5. Compare that condition name to a list of available conditions in worksheet A. If a match is found (within worksheet A), and the condition complexity (located 8 columns to the right) has a value equal to the current iteration of tables the loop is currently on (x), then we increase the count for that employee until that employee is no longer found at which point we insert their count (i) next to their name via (offset) specified by the user.

6. Once this process is complete for all employees for the specified table we are on (x), we move on to the next table and repeat the process.

I have verified the counts manually, and have also verified them using an xlook up and the code appears to get some counts accurately while others are up by 1 or down by 1. Looking for any possible solutions I could be missing. Below is the code for reference:

Sub PullAllConditions()

‘Establish variables

Dim rng As Range, importRng As Range, condition As Range, condition2 As Range, originalNameRange As Range

Dim searchNameRange As Range, totalSearch As Range, updatedHeader As Range, complexityNameRange1 As Range, complexityNameRange2 As Range

Dim targetName As String, searchHeader As String, searchHeader2 As String, conditionName As String, missingConditions As String

Dim columnNameTest As Boolean, found As Boolean

Dim i As Integer, x As Integer, y As Integer, targetMonth As Integer

Dim lc As Long

Dim ques As VbMsgBoxResult

Dim tbl As ListObject, compTBL As ListObject

Dim ws As Worksheet, wsNext As Worksheet, ws2 As Worksheet

Dim nextWb As Workbook

Dim dict As Object

Dim missingValues As Collection

Dim val As Variant

‘Establish key sheets on annual report

Set ws = ThisWorkbook.Sheets(“Production”)

Set ws2 = ThisWorkbook.Sheets(“Conditions by Complexity”)

‘Go first conditions table to start

Call Complexity1Nav

‘Ensure the only two open workbooks are the target workbook and the annual production workbook

ques = MsgBox(“Please make sure that the following is true before starting:” & vbNewLine & vbNewLine & _
“1. The only two workbooks you have open are the ANNUAL PRODUCTION REPORT and CONDITIONS REPORT downloaded from NCINO.” & vbNewLine & vbNewLine & _
“2. Please make sure that the currently visible/ active workbook is the annual report.” & vbNewLine & vbNewLine & _
“If the above is TRUE, click “”Yes”” to proceed. Otherwise, please click “”NO”” to avoid errors.”, vbYesNo)

If ques = vbNo Then

MsgBox “Operating cancelled.”, vbInformation

Exit Sub

End If

‘Acquire a target month from the user

On Error Resume Next

targetMonth = InputBox(“Please enter the target month # this data is for.”, “ENTER TARGET MONTH”)

On Error GoTo 0

If IsNumeric(targetMonth) = False Or targetMonth < 1 Or targetMonth > 12 Then

MsgBox “Operation cancelled or invalid input detected.”, vbCritical

Exit Sub

End If

‘Disable screen updating

Application.ScreenUpdating = True

‘Establish key range/ table references

Set compTBL = ws2.ListObjects(“CRT”)

Set complexityNameRange1 = compTBL.ListColumns(1).DataBodyRange

‘Activate next workbook and set variables to identify it

ActiveWindow.ActivateNext

Set nextWb = ActiveWorkbook

Set wsNext = nextWb.ActiveSheet

‘Establish target column to search employees on

searchHeader = “Condition: Last Modified By”

For Each rng In wsNext.Range(Range(“A1”), Range(“A1”).End(xlToRight))

If rng.Value = searchHeader Then

Set searchNameRange = rng.Offset(1, 0)

columnNameTest = True

Exit For

End If

Next rng

‘Error handling if the column header name is not found in the NCINO report

If columnNameTest = False Then

MsgBox “It appears that the name of the column containing the employee names has changed on the NCINO report or the NCINO report is NOT OPEN. Please select the correct column header cell where employee names are located in the NCINO report in the following step for the operation to continue.” & vbNewLine & vbNewLine & _
“Please note: This operation will get you through the report for today, but please let support know about the error encountered so it is not encountered again in the future. If your NCINO report is NOT OPEN, please cancel this next step and start over.”, vbCritical, “NCINO Report Error!”

On Error Resume Next

Set updatedHeader = Application.InputBox(“Please select the correct column header containing employee names. Please be aware that the cell will NOT become active when you click on it, so pay attention to this box as you only need to click on the desired cell once.”, “Provide Updated Target Column Name”, Type:=8)

On Error GoTo 0

‘Exit sub if user does not select a new target column range to search employees by

If updatedHeader Is Nothing Then

MsgBox “Operating cancelled.”, vbInformation

Exit Sub

Else

searchHeader = updatedHeader.Value

End If

‘Restart loop with updated name once provided

For Each rng In wsNext.Range(Range(“A1”), Range(“A1”).End(xlToRight))

If rng.Value = searchHeader Then

Set searchNameRange = rng.Offset(1, 0)

columnNameTest = True

Exit For

End If

Next rng

End If

‘Proceed with establishing final dynamic search range for employee names

Set totalSearch = wsNext.Range(searchNameRange, searchNameRange.End(xlDown))

‘reset boolean for further use

columnNameTest = False

‘Establish column to search condition names on

searchHeader2 = “Condition Type”

For Each rng In wsNext.Range(Range(“A1”), Range(“A1”).End(xlToRight))

If rng.Value = searchHeader2 Then

columnNameTest = True

Set complexityNameRange2 = wsNext.Range(rng.Offset(1, 0), rng.Offset(1, 0).End(xlDown))

Exit For

End If

Next rng

complexityNameRange2.Select

‘Error handling if the condition name column header name is not found in the NCINO report

If columnNameTest = False Then

MsgBox “It appears that the name of the column containing the condition names has changed on the NCINO report. Please provide the updated header name appearing in the report in the following step for the operation to continue.” & vbNewLine & vbNewLine & _
“Please note: This operation will get you through the report for today, but please let support know about the error encountered so it is not encountered again in the future.”, vbCritical, “NCINO Report Error! (Name of column header has changed)”

On Error Resume Next

Set updatedHeader = Application.InputBox(“Please select the correct column header containing condition names. Please be aware that the cell will NOT become active when you click on it, so pay attention to this box as you only need to click on the desired cell once.”, “Provide Updated Target Column Name”, Type:=8)

On Error GoTo 0

‘Exit sub if user does not select a new target column range to search employees by

If updatedHeader Is Nothing Then

MsgBox “Operation cancelled.”, vbInformation

Exit Sub

Else

‘Re-establish header to search by and run loop again

searchHeader2 = updatedHeader.Value

For Each rng In wsNext.Range(Range(“A1”), Range(“A1”).End(xlToRight))

If rng.Value = searchHeader2 Then

columnNameTest = True

Set complexityNameRange2 = wsNext.Range(rng.Offset(1, 0), rng.Offset(1, 0).End(xlDown))

Exit For

End If

Next rng

End If

End If

‘Return to annual report view for the rest of the procedure (YOU DO NOT NEED TO ACTIVATE SHEETS TO LOOP THROUGH RANGES!)

ws.Activate

‘Add all condition types from our conditions by complexity sheet to a data base. Then, compare conditions from the NCINO report to it to
‘see if we are missing any in our conditions by complexity sheet

Set dict = CreateObject(“Scripting.Dictionary”)

dict.compareMode = vbTextCompare

‘Add those values to the dictionary

For Each rng In complexityNameRange1

If Not dict.exists(rng.Value) Then

dict.Add rng.Value, Nothing

End If

Next rng

‘Start a collection to store missing values

Set missingValues = New Collection

‘Check each value in the NCINO REPORT against the values from the CONDITIONS BY COMPLEXITY sheet to see what is missing

For Each condition In complexityNameRange2

If Not dict.exists(condition.Value) Then

missingValues.Add condition.Value

End If

Next condition

‘Initialize missing values placeholder

missingConditions = “”

‘Print missing conditions to a message box

If missingValues.Count > 0 Then

For Each val In missingValues

missingConditions = missingConditions & val & vbNewLine

Next val

MsgBox “These conditions were on the NCINO report, but not our “”Conditions by Compelxtiy Report””. Address these and re-run the operation afterwards.” & vbNewLine & vbNewLine & missingConditions, vbCritical, “Conditions Not Identified!”

Exit Sub

Else

MsgBox “All conditions exported to the NCINO report were found on our “”Conditions by Complexity Report””. Click “”Ok”” to proceed.”, vbInformation

End If

‘Loop through tables complexity level by complexity level

For x = 1 To 6

For Each tbl In ws.ListObjects

If tbl.Name = “Complexity” & x Then

Set originalNameRange = tbl.ListColumns(1).DataBodyRange

‘Establish employee names you are looking for within each table and reset conditions counter back to 0
For Each rng In originalNameRange

i = 0

targetName = rng.Value

‘In this loop we match the employee name we have established to an employee name in the conditions import worksheet/workbook. We also
‘set our condition based on the condition attached to that employee name in the conditions import worksheet/ workbook.

For Each importRng In totalSearch

If importRng.Value = targetName Then

conditionName = complexityNameRange2.Cells(importRng.Row, 1).Value

‘ In this loop we match a condition from our complexities table to the one pulled from the conditions import worksheet/ workbook
‘We then only add it to the conditions counter if it meets the current value of x which iterates from 1-6

For Each condition In complexityNameRange1

If condition.Value = conditionName And condition.Offset(0, 8).Value = x Then

i = i + 1

End If

Next condition

End If

Next importRng

If i = 0 Then

rng.Offset(0, targetMonth).Value = “”

Else

rng.Offset(0, targetMonth).Value = i

End If

Next rng

End If

Next tbl

Next x

‘Give the user an ending message
MsgBox “Target data has been pulled for month #” & targetMonth, vbInformation

End Sub