Optimizing VBA for Speed

Veritan

Active Member
Joined
Jun 21, 2016
Messages
383
Hello all. I am trying to speed up some code that I have written. What I have works perfectly fine, but is quite slow. I would like to see if there is a way to increase the speed.

My workbook consists of 3 sheets. The first tab (named "Access Data") contains a table (named "tblData") with 5 fields. The headers in sequential order are "User", "Dept", "Module", "Header", and "Job Name". The 2 relevant fields are the "User" and "Job Name" fields. The "User" field contains a series of user names. The "Job Name" field contains all the different jobs that they are able to perform. The table is sorted first by "User", then by "Job Name", both in ascending order. Each User can have multiple jobs, but a given job can only appear once for a specific user. However, a given job may appear for more than 1 user.

The 2nd sheet (named "SOD Testing") is just a repository for the results of the code execution. Cell B1 contains a simple title string ("List of Duties/Jobs"). Cell B2 contains the formula
Excel Formula:
=SORT(UNIQUE(tblData[Job Name]))
Cell C1 contains the formula
Excel Formula:
=TRANSPOSE(SORT(UNIQUE(tblData[Job Name])))
This results in sorted lists of the unique Job Names. The rest of this sheet is filled out by VBA.

The 3rd sheet (named "SOD Notifications") is used to hold a list of the users and the jobs that they perform. However, only certain users and jobs qualify to be on this list (I'll explain more later). Range A1:C1 contains some simple headers ("User", "Job Name 1", "Job Name 2") and the rest of the sheet is blank, but is filled out by VBA.

My goal is 2-fold. First, based on the grid established on "SOD Testing", I want to know how many users are able to do each job listed there. For example, if cell B4 contains "Checks and Registers" and cell E1 contains "Manual Checks", then cell E4 should contain the number of users who are able to do both "Checks and Registers" and "Manual Checks". If there are 3 users that are able to do both jobs, then cell E4 should contain the number 3. Second, certain jobs are not allowed to be performed by the same user. On the "SOD Testing" sheet, the intersecting cell of these jobs is highlighted in red. For example, if cell B10 contains "Void Checks" and cell F1 contains "Prepare Check Proofing" and these two jobs are not allowed to be performed by the same user, then cell F10 would be highlighted in red. This is done manually and is done before running any code.

If my code detects that there is a conflict (in other words, if it identifies that the same user is allowed to perform two conflicting jobs), then it lists the User and both Job Names on the "SOD Notifications" tab starting in cell A2.

Currently, my tblData contains almost 2,000 records, and there are almost 300 unique Job Names. This is a smaller sampling size, and will get larger when we use this for other scenarios. As a result, I'm hoping to find a way to run this code quickly. I have come up with 2 different solutions. Both of these work perfectly. However, they are both fairly slow. In order to speed up the process, I am hoping that somebody can provide some insight into speeding this up. Thank you very much for any and all assistance!

Edit: I'm reasonably comfortable with Power Query and Power Pivot, so solutions involving them are perfectly fine as well.

Note: Both code segments are called while the "SOD Testing" sheet is the active sheet.

Version 1 (no arrays)
VBA Code:
Sub Check_Duties()
    Dim wsData As Worksheet, strFirstAddress As String
    Dim r As Range, strJob As String, rngJob As Range, strUser As String, intOccurrences As Integer
    Dim intTotal As Integer, wsNotifications As Worksheet, rngDuties As Range, msgContinue As VbMsgBoxResult
    Dim loData As ListObject, lngFoundRow As Long
   
    msgContinue = MsgBox(Prompt:="CAUTION!! This process can take several minutes to complete. " & _
                                 "During this time, you will not be able to use Excel. " & _
                                 "Are you sure you wish to continue?", _
                         Buttons:=vbYesNo + vbExclamation + vbDefaultButton2, _
                         Title:="Extended Processing Time Required")
    If msgContinue = vbNo Then Exit Sub
                   
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
   
    'Set the major variables that will be used throughout the code
    Set wsData = Sheets("Access Data")
    Set wsNotifications = Sheets("SOD Notifications")
    Set loData = wsData.ListObjects(1)
    Set rngDuties = Range(Cells(2, "C"), Cells(Cells(Rows.Count, "B").End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
   
    'Clear any existing data to start fresh. Note that cell fill colors are intentionally left intact.
    wsNotifications.Range("A2:C" & wsNotifications.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row).ClearContents
    rngDuties.ClearContents
   
    For Each r In rngDuties
        'Make sure that the row and column values are different.
        If Cells(r.Row, "B") <> Cells(1, r.Column) Then
            strJob = Cells(r.Row, "B")
            Set rngJob = loData.ListColumns("Job Name").DataBodyRange.Find(What:=strJob, LookAt:=xlWhole)
            If Not rngJob Is Nothing Then
                lngFoundRow = loData.ListRows(rngJob.Row - loData.HeaderRowRange.Row).Index
                'Set the exit condition for the Do loop.
                strFirstAddress = rngJob.Address
                'Reset the count of users.
                intTotal = 0
                Do
                    intOccurrences = 0
                    strUser = loData.ListColumns("User").DataBodyRange(lngFoundRow)
                    'Find out if there are any occurrences where the given user has the Job Name that is presented in Row 1 on the active sheet.
                    intOccurrences = WorksheetFunction.CountIfs(loData.ListColumns("User").DataBodyRange, strUser, loData.ListColumns("Job Name").DataBodyRange, Cells(1, r.Column))
                    'If there are any undesired combinations of duties, highlighting the cell in red will cause the SOD Notifications tab to _
                     display the user and their conflict of duties.
                    If r.Interior.Color = vbRed And intOccurrences > 0 Then
                        With wsNotifications
                            .Cells(Rows.Count, "A").End(xlUp).Offset(1) = strUser
                            .Cells(Rows.Count, "B").End(xlUp).Offset(1) = strJob
                            .Cells(Rows.Count, "C").End(xlUp).Offset(1) = Cells(1, r.Column)
                        End With
                    End If
                    'Track the total number of users with the given Job Name combination.
                    intTotal = intTotal + intOccurrences
                    Set rngJob = loData.ListColumns("Job Name").DataBodyRange.FindNext(rngJob)
                Loop Until rngJob.Address = strFirstAddress
                'Print the final total in the cell.
                r = intTotal
            End If
        End If
    Next r
   
   With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With

End Sub

Version 2 (with arrays)
VBA Code:
Sub Check_Duties_Array()
    Dim wsData As Worksheet, wsNotifications As Worksheet, r As Range, i As Long, j As Integer, intTotal As Integer
    Dim msgContinue As VbMsgBoxResult, rngDuties As Range, arrData As Variant
    Dim strJob As String, strCompare As String, blnNotify As Boolean
   
    msgContinue = MsgBox(Prompt:="CAUTION!! This process can take several minutes to complete. " & _
                                 "During this time, you will not be able to use Excel. " & _
                                 "Are you sure you wish to continue?", _
                         Buttons:=vbYesNo + vbExclamation + vbDefaultButton2, _
                         Title:="Extended Processing Time Required")
    If msgContinue = vbNo Then Exit Sub
                   
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
   
    'Set the major variables that will be used throughout the code
    Set wsData = Sheets("Access Data")
    Set wsNotifications = Sheets("SOD Notifications")
    Set rngDuties = Range(Cells(2, "C"), Cells(Cells(Rows.Count, "B").End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
   
    'Clear any existing data to start fresh. Note that cell fill colors are intentionally left intact.
    wsNotifications.Range("A2:C" & wsNotifications.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row).ClearContents
    rngDuties.ClearContents
   
    'Create a 2 dimensional array with the "User" and "Job Name" fields in it.
    arrData = wsData.ListObjects(1).DataBodyRange.Value2
    arrData = Application.Index(arrData, Evaluate("row(1:" & UBound(arrData) & ")"), Array(1, 5))
       
    For Each r In rngDuties
        strJob = Cells(r.Row, "B")
        strCompare = Cells(1, r.Column)
        If r.Interior.Color = vbRed Then blnNotify = True
        'Make sure that the row and column values are different.
        If strJob <> strCompare Then
            'Reset the count of users.
            intTotal = 0
            For i = 1 To UBound(arrData, 1)
                If arrData(i, 2) = strJob Then
                    For j = 1 To UBound(arrData, 1)
                        If arrData(j, 1) = arrData(i, 1) And arrData(j, 2) = strCompare Then
                            intTotal = intTotal + 1
                            'If there are any undesired combinations of duties, highlighting the cell in red will cause the SOD Notifications tab to _
                             display the user and their conflict of duties.
                            If blnNotify Then
                                With wsNotifications
                                    .Cells(Rows.Count, "A").End(xlUp).Offset(1) = arrData(i, 1)
                                    .Cells(Rows.Count, "B").End(xlUp).Offset(1) = arrData(i, 2)
                                    .Cells(Rows.Count, "C").End(xlUp).Offset(1) = strCompare
                                End With
                            End If
                        End If
                    Next j
                End If
            Next i
        r = intTotal
        blnNotify = False
        End If
    Next r
   
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With

End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You could share a sample of your data from sheets 1 and 2. And the expected result on sheet 3.
To be able to test the macros, understand the operation and find to improve the performance of the process.

For examples use XL2BB tool minisheet
 
Upvote 0
Certainly, thanks for pointing that out. This is my first time using XL2BB, so hopefully I am doing this right.

Sample data from my "Access Data" sheet. User ID's have been replaced with generic ID's and some Jobs have been removed to keep the size down, but otherwise, all data is the same as the original.
SOD Testing v2.xlsm
ABCDE
1UserDeptModuleHeaderJob Name
2User1Shared ServicesAccounts PayableTransactionsACH Export
3User1Shared ServicesAccounts PayableTransactionsAP Batch Entry - New
4User1Shared ServicesAccounts PayableTransactionsCreate AP Invoice from Receipts
5User1Shared ServicesAccounts PayableTransactionsManual Checks
6User2Shared ServicesAccounts PayableTransactionsACH Export
7User2Shared ServicesAccounts PayableTransactionsAP Batch Entry - New
8User2Shared ServicesAccounts PayableTransactionsCreate AP Invoice from Receipts
9User2Shared ServicesAccounts PayableTransactionsManual Checks
10User3Customer ServiceOrder EntryTransactionsApply Router Changes to Rate Tables
11User3Customer ServiceOrder EntryTransactionsBill of Lading - Delete
12User3Customer ServiceOrder EntryTransactionsClear EDI Invoice Selection File
13User3Customer ServiceOrder EntryTransactionsGenerate EDI Invoices
14User3Customer ServiceOrder EntryTransactionsInvoicing
15User3Customer ServiceOrder EntryTransactionsMaster Bill of Lading - Open
16User3Customer ServiceInventoryTransactionsCalculate Next Cycle Date
17User3Customer ServiceInventoryTransactionsInventory Transfers
18User3Customer ServiceInventoryTransactionsPart Allocations
19User4PurchasingPurchasingTransactionsLast Cost vs. Standard Cost
20User4PurchasingPurchasingTransactionsPO Receipts by Container
21User4PurchasingInventoryTransactionsCalculate Next Cycle Date
22User4PurchasingInventoryTransactionsInventory Transfers
23User4PurchasingInventoryTransactionsPart Allocations
24User4PurchasingShop Floor ControlTransactionsCharge Material/Expenses (Standalone)
25User4PurchasingShop Floor ControlTransactionsEdit Flexible Schedule
26User4PurchasingShop Floor ControlAdministrationDelete Work Orders
27User4PurchasingAdvanced Planning SystemTransactionsMoveable Dispatch Chart
28User4PurchasingOn Line SystemTransactionsDaily Balancing (No Dollars)
29User4PurchasingShipping & ReceivingTransactionsPO Receipts by Container
30User5Plant SupervisorOrder EntryTransactionsApply Router Changes to Rate Tables
31User5Plant SupervisorOrder EntryTransactionsBill of Lading - Delete
32User5Plant SupervisorOrder EntryTransactionsClear EDI Invoice Selection File
33User5Plant SupervisorOrder EntryTransactionsGenerate EDI Invoices
34User5Plant SupervisorOrder EntryTransactionsInvoicing
35User5Plant SupervisorOrder EntryTransactionsMaster Bill of Lading - Open
36User5Plant SupervisorInventoryTransactionsCalculate Next Cycle Date
37User5Plant SupervisorInventoryTransactionsInventory Transfers
38User5Plant SupervisorInventoryTransactionsPart Allocations
39User5Plant SupervisorShop Floor ControlTransactionsCharge Material/Expenses (Standalone)
40User5Plant SupervisorShop Floor ControlTransactionsEdit Flexible Schedule
41User5Plant SupervisorOn Line SystemTransactionsDaily Balancing (No Dollars)
42User5Plant SupervisorShipping & ReceivingTransactionsPO Receipts by Container
43User6OperationsInventoryTransactionsCalculate Next Cycle Date
44User6OperationsInventoryTransactionsInventory Transfers
45User6OperationsInventoryTransactionsPart Allocations
46User6OperationsShop Floor ControlTransactionsCharge Material/Expenses (Standalone)
47User6OperationsShop Floor ControlTransactionsEdit Flexible Schedule
48User6OperationsShipping & ReceivingTransactionsPO Receipts by Container
49User7Shared ServicesAccounts PayableTransactionsACH Export
50User7Shared ServicesAccounts PayableTransactionsAP Batch Entry - New
51User7Shared ServicesAccounts PayableTransactionsCreate AP Invoice from Receipts
52User7Shared ServicesAccounts PayableTransactionsManual Checks
53User8Shared ServicesAccounts PayableTransactionsACH Export
54User8Shared ServicesAccounts PayableTransactionsAP Batch Entry - New
55User8Shared ServicesAccounts PayableTransactionsCreate AP Invoice from Receipts
56User8Shared ServicesAccounts PayableTransactionsManual Checks
57User10Shared ServicesAccounts PayableTransactionsACH Export
58User10Shared ServicesAccounts PayableTransactionsAP Batch Entry - New
59User10Shared ServicesAccounts PayableTransactionsCreate AP Invoice from Receipts
60User10Shared ServicesAccounts PayableTransactionsManual Checks
61User11Customer ServiceOrder EntryTransactionsApply Router Changes to Rate Tables
62User11Customer ServiceOrder EntryTransactionsBill of Lading - Delete
63User11Customer ServiceOrder EntryTransactionsClear EDI Invoice Selection File
64User11Customer ServiceOrder EntryTransactionsGenerate EDI Invoices
65User11Customer ServiceOrder EntryTransactionsInvoicing
66User11Customer ServiceOrder EntryTransactionsMaster Bill of Lading - Open
67User12PurchasingPurchasingTransactionsLast Cost vs. Standard Cost
68User12PurchasingPurchasingTransactionsPO Receipts by Container
69User12PurchasingInventoryTransactionsCalculate Next Cycle Date
70User12PurchasingInventoryTransactionsInventory Transfers
71User12PurchasingInventoryTransactionsPart Allocations
72User12PurchasingShop Floor ControlTransactionsCharge Material/Expenses (Standalone)
73User12PurchasingShop Floor ControlTransactionsEdit Flexible Schedule
74User12PurchasingShop Floor ControlAdministrationDelete Work Orders
75User12PurchasingAdvanced Planning SystemTransactionsMoveable Dispatch Chart
76User12PurchasingOn Line SystemTransactionsDaily Balancing (No Dollars)
77User12PurchasingShipping & ReceivingTransactionsPO Receipts by Container
78User13Customer ServiceOrder EntryTransactionsApply Router Changes to Rate Tables
79User13Customer ServiceOrder EntryTransactionsBill of Lading - Delete
80User13Customer ServiceOrder EntryTransactionsClear EDI Invoice Selection File
81User13Customer ServiceOrder EntryTransactionsGenerate EDI Invoices
82User13Customer ServiceOrder EntryTransactionsInvoicing
83User13Customer ServiceOrder EntryTransactionsMaster Bill of Lading - Open
84User13Customer ServiceInventoryTransactionsCalculate Next Cycle Date
85User13Customer ServiceInventoryTransactionsInventory Transfers
86User13Customer ServiceInventoryTransactionsPart Allocations
87User14PurchasingPurchasingTransactionsLast Cost vs. Standard Cost
88User14PurchasingPurchasingTransactionsPO Receipts by Container
89User14PurchasingInventoryTransactionsCalculate Next Cycle Date
90User14PurchasingInventoryTransactionsInventory Transfers
91User14PurchasingInventoryTransactionsPart Allocations
92User14PurchasingShop Floor ControlTransactionsCharge Material/Expenses (Standalone)
93User14PurchasingShop Floor ControlTransactionsEdit Flexible Schedule
94User14PurchasingShipping & ReceivingTransactionsPO Receipts by Container
95User15Kitting InventoryTransactionsCalculate Next Cycle Date
96User15Kitting InventoryTransactionsInventory Transfers
97User15Kitting InventoryTransactionsPart Allocations
98User15Kitting Shop Floor ControlTransactionsCharge Material/Expenses (Standalone)
99User15Kitting Shop Floor ControlTransactionsEdit Flexible Schedule
100User16Project ManagerShop Floor ControlTransactionsCharge Material/Expenses (Standalone)
101User16Project ManagerShop Floor ControlTransactionsEdit Flexible Schedule
102User16Project ManagerAdvanced Planning SystemTransactionsMoveable Dispatch Chart
103User17PurchasingPurchasingTransactionsLast Cost vs. Standard Cost
104User17PurchasingPurchasingTransactionsPO Receipts by Container
105User17PurchasingPurchasingAdministrationPurchase Orders In Use
106User17PurchasingInventoryTransactionsCalculate Next Cycle Date
107User17PurchasingInventoryTransactionsInventory Transfers
108User17PurchasingInventoryTransactionsPart Allocations
109User17PurchasingInventoryAdministrationInventory Master BIN Mass Update (c)
110User17PurchasingShop Floor ControlTransactionsCharge Material/Expenses (Standalone)
111User17PurchasingShop Floor ControlTransactionsEdit Flexible Schedule
112User17PurchasingAdvanced Planning SystemTransactionsMoveable Dispatch Chart
113User17PurchasingOn Line SystemTransactionsDaily Balancing (No Dollars)
114User17PurchasingShipping & ReceivingTransactionsPO Receipts by Container
115User18OperationsInventoryTransactionsCalculate Next Cycle Date
116User18OperationsInventoryTransactionsInventory Transfers
117User18OperationsInventoryTransactionsPart Allocations
118User18OperationsShop Floor ControlTransactionsCharge Material/Expenses (Standalone)
119User18OperationsShop Floor ControlTransactionsEdit Flexible Schedule
120User18OperationsShipping & ReceivingTransactionsPO Receipts by Container
121User19PurchasingInventoryTransactionsCalculate Next Cycle Date
122User19PurchasingInventoryTransactionsInventory Transfers
123User19PurchasingInventoryTransactionsPart Allocations
124User19PurchasingShop Floor ControlTransactionsCharge Material/Expenses (Standalone)
125User19PurchasingShop Floor ControlTransactionsEdit Flexible Schedule
126User20Shared ServicesAccounts ReceivableTransactionsAR Batches - Open
127User20Shared ServicesAccounts ReceivableTransactionsAR Remittance from EDI Transactions
128User20Shared ServicesAccounts ReceivableTransactionsCalculate Commissions
129User20Shared ServicesAccounts ReceivableTransactionsConvert Prospect to Customer
130User20Shared ServicesAccounts ReceivableTransactionsFactor Cash Receipt
131User20Shared ServicesOrder EntryTransactionsApply Router Changes to Rate Tables
132User20Shared ServicesOrder EntryTransactionsBill of Lading - Delete
133User20Shared ServicesOrder EntryTransactionsClear EDI Invoice Selection File
134User20Shared ServicesOrder EntryTransactionsGenerate EDI Invoices
135User20Shared ServicesOrder EntryTransactionsInvoicing
136User20Shared ServicesOrder EntryTransactionsMaster Bill of Lading - Open
137User21OperationsInventoryTransactionsCalculate Next Cycle Date
138User21OperationsInventoryTransactionsInventory Transfers
139User21OperationsInventoryTransactionsPart Allocations
140User22OperationsOrder EntryTransactionsApply Router Changes to Rate Tables
141User22OperationsOrder EntryTransactionsBill of Lading - Delete
142User22OperationsOrder EntryTransactionsClear EDI Invoice Selection File
143User22OperationsOrder EntryTransactionsGenerate EDI Invoices
144User22OperationsOrder EntryTransactionsInvoicing
145User22OperationsOrder EntryTransactionsMaster Bill of Lading - Open
146User22OperationsPurchasingTransactionsLast Cost vs. Standard Cost
147User22OperationsPurchasingTransactionsPO Receipts by Container
148User22OperationsInventoryTransactionsCalculate Next Cycle Date
149User22OperationsInventoryTransactionsInventory Transfers
150User22OperationsInventoryTransactionsPart Allocations
151User22OperationsQuality SystemTransactionsMaintain Cause and Corrective Action
152User22OperationsQuality SystemTransactionsMaintain Engineering Change Control - Delete
153User22OperationsShop Floor ControlTransactionsCharge Material/Expenses (Standalone)
154User22OperationsShop Floor ControlTransactionsEdit Flexible Schedule
155User22OperationsAdvanced Planning SystemTransactionsMoveable Dispatch Chart
156User22OperationsShipping & ReceivingTransactionsPO Receipts by Container
157User23OperationsShop Floor ControlTransactionsCharge Material/Expenses (Standalone)
158User23OperationsShop Floor ControlTransactionsEdit Flexible Schedule
159User25ReceivingInventoryTransactionsCalculate Next Cycle Date
160User25ReceivingInventoryTransactionsInventory Transfers
161User25ReceivingInventoryTransactionsPart Allocations
162User25ReceivingShipping & ReceivingTransactionsPO Receipts by Container
163User26ReceivingInventoryTransactionsCalculate Next Cycle Date
164User26ReceivingInventoryTransactionsInventory Transfers
165User26ReceivingInventoryTransactionsPart Allocations
166User26ReceivingShipping & ReceivingTransactionsPO Receipts by Container
167User27ReceivingInventoryTransactionsCalculate Next Cycle Date
168User27ReceivingInventoryTransactionsInventory Transfers
169User27ReceivingInventoryTransactionsPart Allocations
170User28AccountingAccounts PayableTransactionsACH Export
171User28AccountingAccounts PayableTransactionsAP Batch Entry - New
172User28AccountingAccounts PayableTransactionsCreate AP Invoice from Receipts
173User28AccountingAccounts PayableTransactionsManual Checks
174User28AccountingAccounts PayableAdministrationRebuild Alternate Keys - Vendors
175User28AccountingGeneral LedgerTransactionsCreate JE for Receipt of Container
176User28AccountingGeneral LedgerTransactionsGL Currency Conversion
177User28AccountingGeneral LedgerTransactionsJournal Entries - Delete
178User28AccountingGeneral LedgerAdministrationGL Batches In Use
179User28AccountingGeneral LedgerAdministrationNet Change Summary by Account
180User28AccountingAccounts ReceivableTransactionsAR Batches - Open
181User28AccountingAccounts ReceivableTransactionsAR Remittance from EDI Transactions
182User28AccountingAccounts ReceivableTransactionsCalculate Commissions
183User28AccountingAccounts ReceivableTransactionsConvert Prospect to Customer
184User28AccountingAccounts ReceivableTransactionsFactor Cash Receipt
185User28AccountingAccounts ReceivableAdministrationAging and Purge Zeroed Invoices
186User28AccountingAccounts ReceivableAdministrationBalance A/R Open Item Currency Exchange Amounts
187User28AccountingAccounts ReceivableAdministrationCredit Card Credit Form
188User28AccountingOrder EntryTransactionsApply Router Changes to Rate Tables
189User28AccountingOrder EntryTransactionsBill of Lading - Delete
190User28AccountingOrder EntryTransactionsClear EDI Invoice Selection File
191User28AccountingOrder EntryTransactionsGenerate EDI Invoices
192User28AccountingOrder EntryTransactionsInvoicing
193User28AccountingOrder EntryTransactionsMaster Bill of Lading - Open
194User28AccountingPurchasingTransactionsLast Cost vs. Standard Cost
195User28AccountingPurchasingTransactionsPO Receipts by Container
196User28AccountingInventoryTransactionsCalculate Next Cycle Date
197User28AccountingInventoryTransactionsInventory Transfers
198User28AccountingInventoryTransactionsPart Allocations
199User28AccountingQuality SystemTransactionsMaintain Cause and Corrective Action
200User28AccountingQuality SystemTransactionsMaintain Engineering Change Control - Delete
201User28AccountingPayrollTransactionsJournal Entries - Payroll Journal Entries
202User28AccountingPayrollTransactionsManual/Void Check Processing - Manual Checks
203User28AccountingPayrollAdministrationEmployee Name Separation
204User28AccountingShop Floor ControlTransactionsCharge Material/Expenses (Standalone)
205User28AccountingShop Floor ControlTransactionsEdit Flexible Schedule
206User28AccountingAdvanced Planning SystemTransactionsMoveable Dispatch Chart
207User28AccountingOn Line SystemTransactionsDaily Balancing (No Dollars)
208User28AccountingShipping & ReceivingTransactionsPO Receipts by Container
209User29IT InventoryAdministrationInventory Master BIN Mass Update (c)
210User32AccountingAccounts PayableTransactionsACH Export
211User32AccountingAccounts PayableTransactionsAP Batch Entry - New
212User32AccountingAccounts PayableTransactionsCreate AP Invoice from Receipts
213User32AccountingAccounts PayableTransactionsManual Checks
214User32AccountingAccounts PayableAdministrationRebuild Alternate Keys - Vendors
215User32AccountingGeneral LedgerTransactionsCreate JE for Receipt of Container
216User32AccountingGeneral LedgerTransactionsGL Currency Conversion
217User32AccountingGeneral LedgerTransactionsJournal Entries - Delete
218User32AccountingGeneral LedgerAdministrationGL Batches In Use
219User32AccountingGeneral LedgerAdministrationNet Change Summary by Account
220User32AccountingAccounts ReceivableTransactionsAR Batches - Open
221User32AccountingAccounts ReceivableTransactionsAR Remittance from EDI Transactions
222User32AccountingAccounts ReceivableTransactionsCalculate Commissions
223User32AccountingAccounts ReceivableTransactionsConvert Prospect to Customer
224User32AccountingAccounts ReceivableTransactionsFactor Cash Receipt
225User32AccountingAccounts ReceivableAdministrationAging and Purge Zeroed Invoices
226User32AccountingAccounts ReceivableAdministrationBalance A/R Open Item Currency Exchange Amounts
227User32AccountingAccounts ReceivableAdministrationCredit Card Credit Form
228User32AccountingOrder EntryTransactionsApply Router Changes to Rate Tables
229User32AccountingOrder EntryTransactionsBill of Lading - Delete
230User32AccountingOrder EntryTransactionsClear EDI Invoice Selection File
231User32AccountingOrder EntryTransactionsGenerate EDI Invoices
232User32AccountingOrder EntryTransactionsInvoicing
233User32AccountingOrder EntryTransactionsMaster Bill of Lading - Open
234User32AccountingOrder EntryAdministrationApply Cust Values to Ship-To Record
235User32AccountingOrder EntryAdministrationDelete Credit Card Audit File
236User32AccountingOrder EntryAdministrationRate Table Copy
237User32AccountingBusiness IntelligenceAdministrationInitialize Financial Statements
238User32AccountingPurchasingTransactionsLast Cost vs. Standard Cost
239User32AccountingPurchasingTransactionsPO Receipts by Container
240User32AccountingPurchasingAdministrationPurchase Orders In Use
241User32AccountingInventoryTransactionsCalculate Next Cycle Date
242User32AccountingInventoryTransactionsInventory Transfers
243User32AccountingInventoryTransactionsPart Allocations
244User32AccountingInventoryAdministrationMaintain Item Master User Titles (Lt/Bn)
245User32AccountingInventoryAdministrationRefresh Inv Alt Cost from Latest Cost
246User32AccountingQuality SystemTransactionsMaintain Cause and Corrective Action
247User32AccountingQuality SystemTransactionsMaintain Engineering Change Control - Delete
248User32AccountingQuality SystemAdministrationPurge Quality
249User32AccountingPayrollTransactionsJournal Entries - Payroll Journal Entries
250User32AccountingPayrollTransactionsManual/Void Check Processing - Manual Checks
251User32AccountingPayrollAdministrationEmployee Name Separation
252User32AccountingBill Of MaterialAdministrationBOM In Use
253User32AccountingEstimating / Routing & Quote ManagementAdministrationCalculate Workcenter MTD and YTD Hours
254User32AccountingEstimating / Routing & Quote ManagementAdministrationMass Replace Materials
255User32AccountingShop Floor ControlTransactionsCharge Material/Expenses (Standalone)
256User32AccountingShop Floor ControlTransactionsEdit Flexible Schedule
257User32AccountingShop Floor ControlAdministrationDelete Work Orders
258User32AccountingShop Floor ControlAdministrationMass Replace Workcenters in Work Orders
259User32AccountingAdvanced Planning SystemTransactionsMoveable Dispatch Chart
260User32AccountingAdvanced Planning SystemAdministrationClear Forecast Data
261User32AccountingOn Line SystemTransactionsDaily Balancing (No Dollars)
262User32AccountingShipping & ReceivingTransactionsPO Receipts by Container
263User33HRPayrollTransactionsJournal Entries - Payroll Journal Entries
264User33HRPayrollTransactionsManual/Void Check Processing - Manual Checks
265User33HRPayrollAdministrationEmployee Name Separation
266User34AccountingAccounts PayableTransactionsACH Export
267User34AccountingAccounts PayableTransactionsAP Batch Entry - New
268User34AccountingAccounts PayableTransactionsCreate AP Invoice from Receipts
269User34AccountingAccounts PayableTransactionsManual Checks
270User34AccountingAccounts PayableAdministrationRebuild Alternate Keys - Vendors
271User34AccountingGeneral LedgerTransactionsCreate JE for Receipt of Container
272User34AccountingGeneral LedgerTransactionsGL Currency Conversion
273User34AccountingGeneral LedgerTransactionsJournal Entries - Delete
274User34AccountingGeneral LedgerAdministrationGL Batches In Use
275User34AccountingGeneral LedgerAdministrationNet Change Summary by Account
276User34AccountingAccounts ReceivableTransactionsAR Batches - Open
277User34AccountingAccounts ReceivableTransactionsAR Remittance from EDI Transactions
278User34AccountingAccounts ReceivableTransactionsCalculate Commissions
279User34AccountingAccounts ReceivableTransactionsConvert Prospect to Customer
280User34AccountingAccounts ReceivableTransactionsFactor Cash Receipt
281User34AccountingAccounts ReceivableAdministrationAging and Purge Zeroed Invoices
282User34AccountingAccounts ReceivableAdministrationBalance A/R Open Item Currency Exchange Amounts
283User34AccountingAccounts ReceivableAdministrationCredit Card Credit Form
284User34AccountingOrder EntryTransactionsApply Router Changes to Rate Tables
285User34AccountingOrder EntryTransactionsBill of Lading - Delete
286User34AccountingOrder EntryTransactionsClear EDI Invoice Selection File
287User34AccountingOrder EntryTransactionsGenerate EDI Invoices
288User34AccountingOrder EntryTransactionsInvoicing
289User34AccountingOrder EntryTransactionsMaster Bill of Lading - Open
290User34AccountingPurchasingTransactionsLast Cost vs. Standard Cost
291User34AccountingPurchasingTransactionsPO Receipts by Container
292User34AccountingInventoryTransactionsCalculate Next Cycle Date
293User34AccountingInventoryTransactionsInventory Transfers
294User34AccountingInventoryTransactionsPart Allocations
295User34AccountingPayrollTransactionsJournal Entries - Payroll Journal Entries
296User34AccountingPayrollTransactionsManual/Void Check Processing - Manual Checks
297User34AccountingPayrollAdministrationEmployee Name Separation
298User34AccountingShop Floor ControlTransactionsCharge Material/Expenses (Standalone)
299User34AccountingShop Floor ControlTransactionsEdit Flexible Schedule
300User34AccountingShipping & ReceivingTransactionsPO Receipts by Container
Access Data


My "SOD Testing" sheet. Several cells are highlighted in red, and I've already run the code on this sheet, so all values are already filled in.
SOD Testing v2.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBC
1List of Duties/JobsACH ExportAging and Purge Zeroed InvoicesAP Batch Entry - NewApply Cust Values to Ship-To RecordApply Router Changes to Rate TablesAR Batches - OpenAR Remittance from EDI TransactionsBalance A/R Open Item Currency Exchange AmountsBill of Lading - DeleteBOM In UseCalculate CommissionsCalculate Next Cycle DateCalculate Workcenter MTD and YTD HoursCharge Material/Expenses (Standalone)Clear EDI Invoice Selection FileClear Forecast DataConvert Prospect to CustomerCreate AP Invoice from ReceiptsCreate JE for Receipt of ContainerCredit Card Credit FormDaily Balancing (No Dollars)Delete Credit Card Audit FileDelete Work OrdersEdit Flexible ScheduleEmployee Name SeparationFactor Cash ReceiptGenerate EDI InvoicesGL Batches In UseGL Currency ConversionInitialize Financial StatementsInventory Master BIN Mass Update (c)Inventory TransfersInvoicingJournal Entries - DeleteJournal Entries - Payroll Journal EntriesLast Cost vs. Standard CostMaintain Cause and Corrective ActionMaintain Engineering Change Control - DeleteMaintain Item Master User Titles (Lt/Bn)Manual ChecksManual/Void Check Processing - Manual ChecksMass Replace MaterialsMass Replace Workcenters in Work OrdersMaster Bill of Lading - OpenMoveable Dispatch ChartNet Change Summary by AccountPart AllocationsPO Receipts by ContainerPurchase Orders In UsePurge QualityRate Table CopyRebuild Alternate Keys - VendorsRefresh Inv Alt Cost from Latest Cost
2ACH Export0800000000000000800000000000000000000080000000000000
3Aging and Purge Zeroed Invoices3303333303303303333300333333003333333033003333600030
4AP Batch Entry - New8000000000000000800000000000000000000080000000000000
5Apply Cust Values to Ship-To Record1111111111111111111111111111101111111111111111211111
6Apply Router Changes to Rate Tables0000000900900900000000000900009900000000009009000000
7AR Batches - Open0000440404000404000000004400000400000000004000000000
8AR Remittance from EDI Transactions0000440404000404000000004400000400000000004000000000
9Balance A/R Open Item Currency Exchange Amounts3330333303303303333300333333003333333033003333600030
10Bill of Lading - Delete0000900000900900000000000900009900000000009009000000
11BOM In Use1111111111111111111111111111101111111111111111211111
12Calculate Commissions0000444040000404000000004400000400000000004000000000
13Calculate Next Cycle Date000019000190000190000000000019000019190000000000190019000000
14Calculate Workcenter MTD and YTD Hours1111111111111111111111111111101111111111111111211111
15Charge Material/Expenses (Standalone)0000000000015000000015015150000000150001500000000150153000000
16Clear EDI Invoice Selection File0000900090090000000000000900009900000000009009000000
17Clear Forecast Data1111111111111111111111111111101111111111111111211111
18Convert Prospect to Customer0000444040400040000000004400000400000000004000000000
19Create AP Invoice from Receipts8080000000000000000000000000000000000080000000000000
20Create JE for Receipt of Container3330333330330330333300333333003333333033003333600030
21Credit Card Credit Form3330333330330330333300333333003333333033003333600030
22Daily Balancing (No Dollars)00000000000606000000066000000060006000000006061200000
23Delete Credit Card Audit File1111111111111111111111111111101111111111111111211111
24Delete Work Orders0000000000030300000030300000003000300000000303600000
25Edit Flexible Schedule0000000000015015000000150150000000150001500000000150153000000
26Employee Name Separation4440444440440440444440044444004444444044004444800040
27Factor Cash Receipt0000444040400040400000000400000400000000004000000000
28Generate EDI Invoices0000900090090090000000000000009900000000009009000000
29GL Batches In Use3330333330330330333330033333003333333033003333600030
30GL Currency Conversion3330333330330330333330033333003333333033003333600030
31Initialize Financial Statements1111111111111111111111111111101111111111111111211111
32Inventory Master BIN Mass Update (c)0000000000020200000020020000002000200000000202420000
33Inventory Transfers000019000190019001900000000000190000190000000000190019000000
34Invoicing0000900090090090000000000090000900000000009009000000
35Journal Entries - Delete3330333330330330333330033333300333333033003333600030
36Journal Entries - Payroll Journal Entries4440444440440440444440044444400444444044004444800040
37Last Cost vs. Standard Cost00000000000808000000808800000008000000000008081600000
38Maintain Cause and Corrective Action0000300030030330000000030030000330033000003303600000
39Maintain Engineering Change Control - Delete0000300030030330000000030030000330033000003303600000
40Maintain Item Master User Titles (Lt/Bn)1111111111111111111111111111110111111111111111211111
41Manual Checks8080000000000000080000000000000000000000000000000000
42Manual/Void Check Processing - Manual Checks4440444440440440444440044444400444444404004444800040
43Mass Replace Materials1111111111111111111111111111110111111111111111211111
44Mass Replace Workcenters in Work Orders1111111111111111111111111111110111111111111111211111
45Master Bill of Lading - Open0000900090090090000000000090000990000000000009000000
46Moveable Dispatch Chart00000000000707000000707700000007000700000000071400000
47Net Change Summary by Account3330333330330330333330033333300333333303300333600030
48Part Allocations000019000190019001900000000000190000191900000000001900000000
49PO Receipts by Container0000000000021021000000210212100000002100021000000002102100000
50Purchase Orders In Use0000000000020200000020020000002200020000000020240000
51Purge Quality1111111111111111111111111111110111111111111111121111
52Rate Table Copy1111111111111111111111111111110111111111111111121111
53Rebuild Alternate Keys - Vendors3330333330330330333330033333300333333303300333360000
54Refresh Inv Alt Cost from Latest Cost1111111111111111111111111111110111111111111111121111
SOD Testing
Cell Formulas
RangeFormula
C1:BC1C1=TRANSPOSE(SORT(UNIQUE(tblData[Job Name])))
B2:B54B2=SORT(UNIQUE(tblData[Job Name]))
Dynamic array formulas.


My "SOD Notifications" sheet, with the results after running the code.
SOD Testing v2.xlsm
ABC
1UserJob Name 1Job Name 2
2User3Calculate Next Cycle DateInvoicing
3User3Calculate Next Cycle DateInvoicing
4User3Calculate Next Cycle DateInvoicing
5User3Calculate Next Cycle DateInvoicing
6User3Calculate Next Cycle DateInvoicing
7User3Calculate Next Cycle DateInvoicing
8User3Calculate Next Cycle DateInvoicing
9User3Calculate Next Cycle DateInvoicing
10User3Calculate Next Cycle DateInvoicing
11User3Calculate Next Cycle DateInvoicing
12User3Calculate Next Cycle DateInvoicing
13User3Calculate Next Cycle DateInvoicing
14User3Calculate Next Cycle DateInvoicing
15User3Calculate Next Cycle DateInvoicing
16User3Calculate Next Cycle DateInvoicing
17User3Calculate Next Cycle DateInvoicing
18User3Calculate Next Cycle DateInvoicing
19User3Calculate Next Cycle DateInvoicing
20User3Calculate Next Cycle DateInvoicing
21User4Edit Flexible ScheduleMoveable Dispatch Chart
22User4Edit Flexible ScheduleMoveable Dispatch Chart
23User4Edit Flexible ScheduleMoveable Dispatch Chart
24User4Edit Flexible ScheduleMoveable Dispatch Chart
25User4Edit Flexible ScheduleMoveable Dispatch Chart
26User4Edit Flexible ScheduleMoveable Dispatch Chart
27User4Edit Flexible ScheduleMoveable Dispatch Chart
28User4Edit Flexible ScheduleMoveable Dispatch Chart
29User4Edit Flexible ScheduleMoveable Dispatch Chart
30User4Edit Flexible ScheduleMoveable Dispatch Chart
31User4Edit Flexible ScheduleMoveable Dispatch Chart
32User4Edit Flexible ScheduleMoveable Dispatch Chart
33User4Edit Flexible ScheduleMoveable Dispatch Chart
34User4Edit Flexible ScheduleMoveable Dispatch Chart
35User4Edit Flexible ScheduleMoveable Dispatch Chart
36User3Generate EDI InvoicesApply Router Changes to Rate Tables
37User3Generate EDI InvoicesApply Router Changes to Rate Tables
38User3Generate EDI InvoicesApply Router Changes to Rate Tables
39User3Generate EDI InvoicesApply Router Changes to Rate Tables
40User3Generate EDI InvoicesApply Router Changes to Rate Tables
41User3Generate EDI InvoicesApply Router Changes to Rate Tables
42User3Generate EDI InvoicesApply Router Changes to Rate Tables
43User3Generate EDI InvoicesApply Router Changes to Rate Tables
44User3Generate EDI InvoicesApply Router Changes to Rate Tables
45User28Manual/Void Check Processing - Manual ChecksPO Receipts by Container
46User28Manual/Void Check Processing - Manual ChecksPO Receipts by Container
47User28Manual/Void Check Processing - Manual ChecksPO Receipts by Container
48User28Manual/Void Check Processing - Manual ChecksPO Receipts by Container
SOD Notifications
 
Upvote 0
This is my first time using XL2BB, so hopefully I am doing this right.
It's perfect!

I am reviewing your code and I can see that you have this line:
If r.Interior.Color = vbRed Then blnNotify = True

That always slows down the process as you have to directly review the content of the cell.
Is there another way to perform that condition, that is, what determines that a cell has its red interior?
 
Upvote 0
With your example, the first code gets 47 records. The second code gets only 30 records :unsure:. If you agree, I consider the first code as an example, to analyze it.
 
Upvote 0
The primary driver is whether or not it is acceptable for a single user to be able to perform 2 separate jobs. The whole point of this is for our internal audit department to know whether or not we are in compliance with SOX Separation of Duties (SOD) rules in the US. For example, it would potentially be unethical for a single person to be able to both post a journal entry as well as review that entry. SOX requires that those 2 functions be performed by 2 separate people in order to minimize the chances of fraud occurring.

I need a way for our auditors to be able to easily identify potentially conflicting jobs that doesn't require any major effort on their part. Highlighting cells in a certain color certainly is simple, but if it's impeding the execution speed, then maybe an alternative approach would be better.

An alternative way of identifying jobs that cannot be performed by the same person might be to have a table with a list of the jobs in one field, and the incompatible jobs in the second field. Perhaps having our audit department enter values into there and then having the code read that table into an array would be one way of speeding it up.

Ultimately, this file is intended for non-power users of Excel, so I need to keep it simple enough for them to be able to use effectively, while still automating the process enough so that they don't have to manually review each one of our thousands of employees :)
 
Upvote 0
With your example, the first code gets 47 records. The second code gets only 30 records :unsure:.
Good catch (y) I didn't see that. I checked the results manually, and my Version 2 code (the one with the arrays) is the one that is providing the correct results. I must have an error in my first one. Thanks for finding!
 
Upvote 0
An alternative way of identifying jobs that cannot be performed by the same person might be to have a table with a list of the jobs in one field, and the incompatible jobs in the second field. Perhaps having our audit department enter values into there and then having the code read that table into an array would be one way of speeding it up.
You work on that alternative and when you have it well defined you go back here and explain it.

At the end of the macro, the cells you require could be highlighted, but that can be done with an object and would be a single step. You wouldn't need to read cell by cell.


my Version 2 code (the one with the arrays) is the one that is providing the correct results
Okay, check out macro2.
 
Upvote 0
I have good news. We can keep the cells red.

I checked your code2 and the result has this problem:
At the end of the data you are repeating 3 records.

1647573681416.png

In columns E, F and G are the correct results.

The following code puts the results in cell E2 onwards. Maybe the code is more extensive, but it will certainly be faster. (And it works with the cells in red.)

VBA Code:
Sub Check_Duties_Array_3()
  Dim a As Variant, b As Variant, c As Range
  Dim wsSod As Worksheet, wsData As Worksheet
  Dim i As Long, j As Long, lr As Long, lc As Long
  Dim f As Range, rngDuties As Range
  Dim dic1 As Object, dic2 As Object, dic3 As Object
  Dim cell As String
  Dim ky1 As Variant, ky2 As Variant, ky3 As Variant
  
  Set wsSod = Sheets("SOD Testing")
  Set wsData = Sheets("Access Data")

  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  Set dic3 = CreateObject("Scripting.Dictionary")
  
  lr = wsSod.Range("B" & Rows.Count).End(3).Row
  lc = wsSod.Cells(1, Columns.Count).End(1).Column
  Set rngDuties = wsSod.Range("C2", wsSod.Cells(lr, lc))
  
  Application.FindFormat.Clear
  Application.FindFormat.Interior.ColorIndex = 3
  Set f = rngDuties.Find("", rngDuties.Cells(1), xlFormulas, xlPart, xlByRows, xlNext, False, SearchFormat:=True)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      dic1(wsSod.Cells(f.Row, "B").Value & "|" & wsSod.Cells(1, f.Column).Value) = Empty
      Set f = rngDuties.Find("", f, xlValues, xlWhole, xlByRows, xlNext, False, SearchFormat:=True)
    Loop While f.Address <> cell
  End If
  Application.FindFormat.Clear
  
  a = wsData.ListObjects(1).DataBodyRange.Value2
  ReDim b(1 To UBound(a, 1), 1 To 3)

  For i = 1 To UBound(a, 1)
    dic2(a(i, 1)) = Empty
    dic3(a(i, 1) & "|" & a(i, 5)) = Empty
  Next
  
  For Each ky3 In dic2.keys     'users
    For Each ky1 In dic1.keys   'red cells
      If dic3.exists(ky3 & "|" & Split(ky1, "|")(0)) And _
         dic3.exists(ky3 & "|" & Split(ky1, "|")(1)) Then
        j = j + 1
        b(j, 1) = ky3
        b(j, 2) = Split(ky1, "|")(0)
        b(j, 3) = Split(ky1, "|")(1)
      End If
    Next
  Next
  
  Sheets("SOD Notifications").Range("E2").Resize(j, 3).Value = b
End Sub

Note:
I need to include the part of the counter intTotal = intTotal + 1, I am still working on it. But I wanted to give you this part of the code for you to review with your data.
 
Upvote 0
So far, this is working extremely well :) It's providing me with a perfect list of the users and the conflicting jobs for each cell in red. Hopefully we can figure out how to get a count of each job intersection. Once that's in place, this will be perfect. Thanks for all your help so far!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top