Excel宏VBA：实现值分配至多列并将订单ID与司机姓名拆分至独立列的技术问询

Let's fix up your code to handle both tasks smoothly: splitting combined Order ID and Driver Name into separate columns, then distributing the processed data across multiple columns in chunks of 44 rows (as your original code intended).

Here's the adjusted code: #

Sub SplitAndDistributeData()
    ' Declare variables
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim splitRange As Range
    Dim outputStartCell As Range
    Dim totalRows As Long
    Dim chunkSize As Integer
    Dim totalColumns As Integer
    Dim distributeArray() As Variant
    Dim i As Long, rowIdx As Integer, colIdx As Integer
    Dim splitText As Variant
    
    ' Set references (adjust sheet name and ranges as needed)
    Set ws = ThisWorkbook.ActiveSheet ' Or use ThisWorkbook.Sheets("YourSheetName")
    chunkSize = 44 ' Number of rows per column chunk
    Set outputStartCell = ws.Range("$C$2") ' Starting cell for distributed data
    
    ' Step 1: Get total rows with data in column A (assuming combined Order-Driver data lives here)
    totalRows = ws.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
    If totalRows < 2 Then ' Skip header row
        MsgBox "No data found in column A!", vbExclamation
        Exit Sub
    End If
    Set sourceRange = ws.Range("$A$2:$A$" & totalRows)
    
    ' Step 2: Split Order ID and Driver Name into separate columns (B and C)
    Set splitRange = ws.Range("$B$2:$C$" & totalRows)
    For i = 1 To sourceRange.Cells.Count
        ' Split text using " - " as delimiter (replace with your actual separator if different)
        splitText = Split(sourceRange.Cells(i).Value, " - ")
        If UBound(splitText) >= 1 Then
            splitRange.Cells(i, 1).Value = Trim(splitText(0)) ' Order ID in column B
            splitRange.Cells(i, 2).Value = Trim(splitText(1)) ' Driver Name in column C
        Else
            ' Handle cases where delimiter isn't found
            splitRange.Cells(i, 1).Value = sourceRange.Cells(i).Value
            splitRange.Cells(i, 2).Value = "N/A"
        End If
    Next i
    
    ' Step 3: Distribute the split data across multiple columns
    totalColumns = WorksheetFunction.RoundUp(totalRows / chunkSize, 0)
    ReDim distributeArray(1 To chunkSize, 1 To totalColumns * 2) ' *2 for Order ID + Driver Name pairs
    
    For i = 0 To totalRows - 1
        ' Calculate positions in the distribution array
        rowIdx = i Mod chunkSize + 1
        colIdx = (Int(i / chunkSize) * 2) + 1 ' Shift by 2 columns per chunk
        
        ' Populate array with split data
        distributeArray(rowIdx, colIdx) = ws.Range("B" & i + 2).Value ' Order ID
        distributeArray(rowIdx, colIdx + 1) = ws.Range("C" & i + 2).Value ' Driver Name
    Next i
    
    ' Write the array to the worksheet
    outputStartCell.Resize(UBound(distributeArray, 1), UBound(distributeArray, 2)).Value = distributeArray
    
    MsgBox "Data split and distributed successfully!", vbInformation
End Sub

Key Adjustments & Explanations: #

• Dual Task Handling: The code first splits combined Order ID/Driver Name from column A into separate columns B and C, then distributes these paired values across multiple columns in chunks of 44 rows.
• Flexible Delimiter: The split uses Split(sourceRange.Cells(i).Value, " - ") — if your combined data uses a different separator (like | or ,), just replace " - " with your actual delimiter.
• Error Handling: Includes checks for missing data and cases where the delimiter isn't found, so you won't hit unexpected crashes.
• Easy Customization: You can tweak the chunk size, source column, output start cell, or sheet name by adjusting the variables at the top of the macro.

How to Use: #

1. Ensure your combined Order ID and Driver Name data is in column A (with a header in A1).
2. Open the VBA editor with Alt + F11.
3. Insert a new module via Insert > Module.
4. Paste this code into the module.
5. Run the macro with F5 or via the Macro menu.

Content of the question originates from Stack Exchange, question author Mohamed Habeeb