如何使用Excel宏根据单元格匹配列表自动修改Box Size列值

Macro Code #

Here's a VBA macro that automates the task:

Sub UpdateBoxSize()
    Dim wsMain As Worksheet
    Dim wsHalf As Worksheet
    Dim halfList As Range
    Dim mainLastRow As Long
    Dim i As Long
    Dim matchResult As Variant
    
    ' Set worksheet references
    Set wsMain = ThisWorkbook.Worksheets("Sheet1") ' Update if your main sheet has a different name
    Set wsHalf = ThisWorkbook.Worksheets("Sheet2")
    Set halfList = wsHalf.Range("HalfBox") ' Uses the named range on Sheet2
    
    ' Get last row with data in Description column (K)
    mainLastRow = wsMain.Cells(wsMain.Rows.Count, "K").End(xlUp).Row
    
    ' Loop through each row to check for matches
    For i = 1 To mainLastRow
        matchResult = Application.Match(wsMain.Cells(i, "K").Value, halfList, 0)
        
        ' Update Box Size to "Half" if match is found
        If Not IsError(matchResult) Then
            wsMain.Cells(i, "A").Value = "Half"
        End If
    Next i
    
    MsgBox "Box Size update finished!", vbInformation
End Sub

How to Implement #

• Press Alt + F11 to open the VBA Editor
• Right-click your workbook in the Project Explorer > Insert > Module
• Paste the code into the new module
• Adjust "Sheet1" in the code if your main data sheet uses a different name
• Run the macro by pressing F5 in the editor, or assign it to a button in your workbook

Key Details #

• The macro uses your existing HalfBox named range on Sheet2 directly—no need to adjust range addresses if you modify the list later
• Matches are case-insensitive (Excel's Match function defaults to this behavior)
• Rows with no matching Description will retain their original "Full" value in column A

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