VBA宏开发需求：匹配两列首尾字符串并输出判定结果

Hey there! Let's walk through building this macro step by step—perfect for a VBA beginner. The goal is to check if the first and last names in Column A (full name) match those in Column B (shortened middle name), then output "OK" or "Check" in Column C.

Here's the Complete Macro Code #

Sub CheckNameMatches()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim fullNameParts As Variant
    Dim inputNameParts As Variant
    Dim fullFirst As String, fullLast As String
    Dim inputFirst As String, inputLast As String
    
    ' Set the worksheet you're working with (change "Sheet1" to your sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Find the last row with data in Column A (so we don't loop empty rows)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row starting from row 2 (assuming row 1 is headers)
    For i = 2 To lastRow
        ' Skip empty cells in A or B to avoid errors
        If ws.Cells(i, "A").Value <> "" And ws.Cells(i, "B").Value <> "" Then
            ' Split the full name (Column A) into an array of words
            fullNameParts = Split(ws.Cells(i, "A").Value, " ")
            ' Split the input name (Column B) into an array of words
            inputNameParts = Split(ws.Cells(i, "B").Value, " ")
            
            ' Get first and last name from Column A
            fullFirst = Trim(fullNameParts(LBound(fullNameParts)))
            fullLast = Trim(fullNameParts(UBound(fullNameParts)))
            
            ' Get first and last name from Column B
            inputFirst = Trim(inputNameParts(LBound(inputNameParts)))
            inputLast = Trim(inputNameParts(UBound(inputNameParts)))
            
            ' Compare first and last names (case-insensitive)
            If StrComp(fullFirst, inputFirst, vbTextCompare) = 0 And _
               StrComp(fullLast, inputLast, vbTextCompare) = 0 Then
                ws.Cells(i, "C").Value = "OK"
            Else
                ws.Cells(i, "C").Value = "Check"
            End If
        Else
            ' If either cell is empty, mark as Check to flag incomplete entries
            ws.Cells(i, "C").Value = "Check"
        End If
    Next i
    
    MsgBox "Name check completed!", vbInformation
End Sub

How to Use This Macro #

1. Open your Excel file.
2. Press Alt + F11 to open the VBA Editor.
3. Right-click your workbook in the Project Explorer (left pane) > Insert > Module.
4. Paste the code above into the new module.
5. Change "Sheet1" in the code to match your actual worksheet name (if needed).
6. Press F5 to run the macro, or assign it to a button for one-click access.

Key Details for Beginners #

• Split() Function: This turns a name string into an array of words (using spaces as separators). For example, "John Jerome Doss" becomes Array("John", "Jerome", "Doss").
• LBound() & UBound(): These grab the first and last elements of the array—so we always get the first and last name, even if there are multiple middle names.
• StrComp(): Compares strings without case sensitivity (thanks to vbTextCompare), so "john" and "John" count as a match.
• Error Prevention: The code skips empty cells to avoid runtime errors if some rows have missing data.

Edge Cases Handled #

• Names with multiple middle names (e.g., "Mary Ann Louise Smith" vs "Mary A L Smith" will still return "OK").
• Case differences (e.g., "john doss" vs "John Doss" is recognized as a match).
• Empty cells in A or B are marked as "Check" to flag incomplete entries.

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