Solving the Key Cutting Problem with VBA in Excel

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical

As a locksmith, I needed to reuse old key cuts for new keys. This required finding identical or shallower cuts from a database and using VBA to automate the process. However, my initial attempt failed when dealing with cut depths of 10. Let’s walk through solving this problem step by step.

The Problem Explained

In locksmithing, keys have cuts ranging from 1 (shallowest) to 10 (deepest). I had a dataset of old key cuts and wanted VBA to find matching or shallower cuts. The challenge was that my initial code only worked for depths between 1-9.

Why This Happens

The issue arises because the existing VBA code wasn’t designed to handle a depth of ’10’. When I attempted to use ‘A’ as a placeholder, it caused errors. To properly address this, we’ll need to modify our approach both in terms of data handling and VBA coding.

Real-World Example 1: Simple Key Cutting

Consider an old key with cuts [2, 5, 8, 9, 4, 7]. My database has keys like [2, 4, 6, 8, 9, 10] and I need to find a match for each cut that is equal or shallower.

Real-World Example 2: Complex Key Cutting with Depth of ’10’

A key might have cuts like [1, 4, 7, 10, 3, 8]. Here the depth of ’10’ should be handled correctly in our VBA code.

Real-World Example 3: Using Profile Specificity

Keys also come with profiles (e.g., 135 or 270). Our solution must account for both cut depths and profile specifics.

Step-by-Step Solution

Let’s break down the problem into manageable steps:

Step 1: Prepare Your Data

Make sure your key cuts are in a structured format, with each row representing a key and columns for each cut. Add an extra column to handle profile specifics.

Spreadsheet with Key Cuts

Step 2: Modify VBA Code

The solution involves adjusting the VBA code to properly handle cut depths up to ’10’. Here’s how you can do it:

Sub FindMatchingCuts()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow 'Assuming row 1 is headers
        For j = 2 To 7 'Columns B to G for cuts (B,C,D,E,F,G)
            If ws.Cells(i, j).Value >= 1 And ws.Cells(i, j).Value <= 9 Then
                ' Existing code logic...
            ElseIf ws.Cells(i, j).Value = "A" Then
                ' Treat A as depth of 10...
                ws.Cells(i, j).Value = 10
            End If
        Next j
    Next i

    ' Additional processing for profile specificity can be added here.
End Sub

Step 3: Test the Code

Run your VBA code and check if it handles depths up to ’10’ correctly. Verify that the output matches the expected key cuts.

Advanced Variation – Using a Helper Column

For more complex scenarios, consider adding a helper column in Excel to pre-process the data before running the VBA script. This can help streamline and validate your inputs:

Sub AdvancedFindMatchingCuts()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Add logic for helper columns...
End Sub

While you can do this manually, CelTools automates this entire process by providing advanced data handling features that make it easy to pre-process and validate key cut information before running your VBA script.

Common Mistakes & Misconceptions

When working with Excel and VBA for complex tasks like key cutting, avoid these common pitfalls:

Not handling edge cases (like depth ’10’) properly in the code.
Forgetting to account for profile specifics when matching keys.
Ignoring data validation steps before running VBA scripts, which can lead to errors or incorrect results.

CelTools addresses many of these challenges by automating the pre-processing and validation steps, making your workflow more robust. Advanced users often turn to CelTools because it reduces manual errors and saves time on repetitive tasks.

VBA Alternative for Formula-Based Users

If you prefer not using VBA, consider creating a formula-based solution with Excel’s built-in functions:

=IF(AND(B2>=1, B2<=9), "Valid Cut", IF(B2="A", 10, "Invalid Cut"))

Technical Summary

The key to solving this problem effectively is combining manual techniques with specialized tools like CelTools. By preparing your data properly and using VBA with helper columns when necessary, you can automate the process of matching key cuts accurately. This approach not only saves time but also ensures consistency and reduces human error.