Excel VBA实现同行两列前4字符匹配标记（适配可变行数）

Hey there! Let's sort out your problem—turning that formula into flexible VBA that handles variable row counts (since your table grows/shrinks daily) while keeping fixed columns. I've got a few solid approaches tailored to your needs:

Method 1: Write the Formula Directly via VBA (Simple & Fast) #

This approach leverages Excel's native formula efficiency, with VBA handling the heavy lifting of targeting the right range automatically. It's cleaner than your original formula too, since we only need to compare the two cells in the same row.

Sub MarkMatchesWithFormula()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim outputCol As Integer ' Column for Match/No Match results
    
    ' Set your worksheet (update "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    outputCol = 3 ' Results go to Column C—adjust if needed
    
    ' Find the last row with data in Column A (adapts to daily changes)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Skip header row (start at row 1 instead if you don't have headers)
    If lastRow >= 2 Then
        ' Bulk-fill the formula across all rows at once (way faster than looping)
        ws.Range(ws.Cells(2, outputCol), ws.Cells(lastRow, outputCol)).Formula = _
            "=IF(LEFT(A2,4)=LEFT(B2,4),""Match"",""No Match"")"
    End If
    
    ' Optional: Convert formulas to static values if you don't need dynamic updates
    ' ws.Range(ws.Cells(2, outputCol), ws.Cells(lastRow, outputCol)).Value = _
    '     ws.Range(ws.Cells(2, outputCol), ws.Cells(lastRow, outputCol)).Value
End Sub

Method 2: VBA Calculation Without Formulas #

If you prefer to avoid formulas entirely and have VBA compute the results directly, here are two options:

Option A: Basic Loop (Easy to Read & Modify) #

Great for small to medium tables, this loop checks each row one by one and writes the result directly.

Sub MarkMatchesWithLoop()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim outputCol As Integer
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    outputCol = 3 ' Column C for results
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through rows starting from row 2 (skip header)
    For i = 2 To lastRow
        ' Compare first 4 characters of Column A and B
        If Left(ws.Cells(i, "A").Value, 4) = Left(ws.Cells(i, "B").Value, 4) Then
            ws.Cells(i, outputCol).Value = "Match"
        Else
            ws.Cells(i, outputCol).Value = "No Match"
        End If
    Next i
End Sub

Option B: Array-Based Approach (Blazing Fast for Large Datasets) #

If your table has thousands of rows, this method is way more efficient—it loads all data into memory first, processes it, then writes back the results in one go.

Sub MarkMatchesWithArray()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataArr As Variant
    Dim resultArr As Variant
    Dim i As Long
    Dim outputCol As Integer
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    outputCol = 3 ' Column C for results
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Load Columns A and B data into an array (from row 2 to last row)
    dataArr = ws.Range(ws.Cells(2, "A"), ws.Cells(lastRow, "B")).Value
    ' Initialize array to hold results
    ReDim resultArr(1 To UBound(dataArr, 1), 1 To 1)
    
    ' Process data in memory (no slow worksheet reads/writes)
    For i = 1 To UBound(dataArr, 1)
        If Left(dataArr(i, 1), 4) = Left(dataArr(i, 2), 4) Then
            resultArr(i, 1) = "Match"
        Else
            resultArr(i, 1) = "No Match"
        End If
    Next i
    
    ' Write results back to the worksheet
    ws.Cells(2, outputCol).Resize(UBound(resultArr, 1), 1).Value = resultArr
End Sub

Quick Notes to Customize: #

• Worksheet Name: Replace "Sheet1" with your actual sheet's name (e.g., "DailyData").
• Output Column: Change outputCol = 3 to the column number you want results in (3 = C, 4 = D, etc.).
• Header Row: All examples start at row 2. If you don't have a header, adjust the starting row to 1 in the code.

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