VBA宏技术问询：基于Scripting Dictionary匹配客户编码返回解码名

Hey there! Let's work through your VBA macro requirements together. I'll share a polished version of the code that handles your customer code matching, plus adds the Scripting Dictionary reverse lookup (finding a key from a value) you need.

This script pulls from your CustomerCodeReference sheet, matches codes in the ReportNumber column of your report, and drops the decoded names into the next empty column. We’ll use a Scripting Dictionary here because it’s faster than repeated VLOOKUP calls, especially with large datasets.

Sub ProcessCustomerCodes()
    Dim wsReport As Worksheet, wsRef As Worksheet
    Dim reportNumCol As Long, nextEmptyCol As Long
    Dim lastRowReport As Long, lastRowRef As Long
    Dim customerDict As Object
    Dim i As Long, currentCode As String
    
    ' Set your worksheet references (update the report sheet name!)
    Set wsReport = ThisWorkbook.Worksheets("YourReportSheet")
    Set wsRef = ThisWorkbook.Worksheets("CustomerCodeReference")
    
    ' Find the column with "ReportNumber" header
    On Error Resume Next
    reportNumCol = wsReport.Rows(1).Find(What:="ReportNumber", LookIn:=xlValues, LookAt:=xlWhole).Column
    On Error GoTo 0
    If reportNumCol = 0 Then
        MsgBox "Could not find 'ReportNumber' column in the report sheet!"
        Exit Sub
    End If
    
    ' Get next empty column for decoded names
    nextEmptyCol = wsReport.Cells(1, wsReport.Columns.Count).End(xlToLeft).Column + 1
    wsReport.Cells(1, nextEmptyCol).Value = "Decoded Customer Name" ' Add header
    
    ' Initialize Scripting Dictionary (key = customer code, item = decoded name)
    Set customerDict = CreateObject("Scripting.Dictionary")
    customerDict.CompareMode = vbTextCompare ' Case-insensitive matching
    
    ' Load reference data into the dictionary
    lastRowRef = wsRef.Cells(wsRef.Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRowRef ' Skip header row (assuming header is row 1)
        currentCode = Trim(wsRef.Cells(i, 1).Value)
        If currentCode <> "" And Not customerDict.Exists(currentCode) Then
            customerDict.Add currentCode, wsRef.Cells(i, 2).Value
        End If
    Next i
    
    ' Match codes in the report and populate decoded names
    lastRowReport = wsReport.Cells(wsReport.Rows.Count, reportNumCol).End(xlUp).Row
    For i = 2 To lastRowReport ' Skip header row
        currentCode = Trim(wsReport.Cells(i, reportNumCol).Value)
        If customerDict.Exists(currentCode) Then
            wsReport.Cells(i, nextEmptyCol).Value = customerDict(currentCode)
        Else
            wsReport.Cells(i, nextEmptyCol).Value = "No Match" ' Handle unmatched codes
        End If
    Next i
    
    ' Cleanup
    Set customerDict = Nothing
    Set wsReport = Nothing
    Set wsRef = Nothing
End Sub

To get the key (customer code) from a value (decoded name), we can add a helper function that loops through the dictionary’s keys. Here’s how to integrate it:

First, add this helper function outside the main sub:

Function GetCustomerCodeByName(customerDict As Object, targetName As String) As String
    Dim key As Variant
    For Each key In customerDict.Keys
        If customerDict(key) = targetName Then
            GetCustomerCodeByName = key
            Exit Function ' Stop at first match (adjust if multiple codes map to one name)
        End If
    Next key
    GetCustomerCodeByName = "" ' Return empty string if no match found
End Function

Then, you can call this function from your main macro or another sub. For example, to find the code for a specific name:

Sub ExampleReverseLookup()
    Dim wsRef As Worksheet
    Dim customerDict As Object
    Dim targetName As String, matchedCode As String
    
    Set wsRef = ThisWorkbook.Worksheets("CustomerCodeReference")
    Set customerDict = CreateObject("Scripting.Dictionary")
    customerDict.CompareMode = vbTextCompare
    
    ' Load reference data (same as before)
    Dim lastRowRef As Long, i As Long
    lastRowRef = wsRef.Cells(wsRef.Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRowRef
        Dim currentCode As String
        currentCode = Trim(wsRef.Cells(i, 1).Value)
        If currentCode <> "" And Not customerDict.Exists(currentCode) Then
            customerDict.Add currentCode, wsRef.Cells(i, 2).Value
        End If
    Next i
    
    ' Test the reverse lookup
    targetName = "Acme Corp" ' Replace with the name you're searching for
    matchedCode = GetCustomerCodeByName(customerDict, targetName)
    
    If matchedCode <> "" Then
        MsgBox "Customer Code for '" & targetName & "' is: " & matchedCode
    Else
        MsgBox "No customer code found for '" & targetName & "'"
    End If
    
    Set customerDict = Nothing
    Set wsRef = Nothing
End Sub

Key Notes: #

• Update YourReportSheet to the actual name of your report worksheet.
• If your CustomerCodeReference sheet has codes in a different column (not column A) or names in a different column (not column B), adjust the Cells(i,1) and Cells(i,2) references accordingly.
• The vbTextCompare makes matching case-insensitive; switch to vbBinaryCompare if you need case-sensitive matches.
• The reverse lookup stops at the first match—if multiple customer codes map to the same name, you’ll need to modify the helper function to return a list instead.

内容的提问来源于stack exchange，提问作者DJCrowen