基于单元格值插入复制行的VBA代码执行结果不符求助

It looks like you're trying to repeat each row in your Excel table according to the value in the Quantity column. The partial code you provided is missing the core logic to insert and copy rows, so let's fix that with a complete, working VBA subroutine.

Step-by-Step Explanation #

The key here is to loop from the bottom of your data upwards (to avoid issues with shifting rows when inserting), then for each row:

1. Grab the quantity value
2. Insert enough rows below to reach the desired total count
3. Copy the original row's data to the new rows

Complete VBA Code #

Sub ExpandRowsByQuantity()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim lastDataRow As Long
    Dim i As Long
    Dim quantity As Long
    Dim rowsToInsert As Long
    
    ' Use the active worksheet (you can change this to a specific sheet like Sheets("Sheet1"))
    Set ws = ActiveSheet
    
    ' Prompt user to select the entire data range (including headers)
    On Error Resume Next
    Set dataRange = Application.InputBox( _
        Prompt:="Select your data range (including the header row):", _
        Title:="Select Data Range", _
        Type:=8)
    On Error GoTo 0
    
    ' Exit if user cancels the input box
    If dataRange Is Nothing Then
        MsgBox "Selection canceled. Exiting macro.", vbExclamation
        Exit Sub
    End If
    
    ' Get the last row of data (excluding the header row)
    lastDataRow = dataRange.Rows.Count
    
    ' Loop from the last data row up to the second row (since row 1 is the header)
    For i = lastDataRow To 2 Step -1
        ' Get the quantity value from the first column of the current row
        quantity = dataRange.Cells(i, 1).Value
        
        ' Only proceed if quantity is a positive number
        If IsNumeric(quantity) And quantity > 0 Then
            ' Adjust this line to match your exact expected output
            ' Standard logic: total rows = quantity (insert quantity-1 rows)
            ' rowsToInsert = quantity - 1
            
            ' Custom logic for your example: quantity=1 gives 2 rows, quantity=N gives N rows
            rowsToInsert = IIf(quantity = 1, 1, quantity - 1)
            
            If rowsToInsert > 0 Then
                ' Insert the required number of rows below the current row
                ws.Rows(i + 1).Resize(rowsToInsert).Insert Shift:=xlDown
                
                ' Copy the current row's data to the newly inserted rows
                dataRange.Cells(i, 1).Resize(1, dataRange.Columns.Count).Copy _
                    Destination:=ws.Cells(i + 1, dataRange.Column).Resize(rowsToInsert, dataRange.Columns.Count)
            End If
        End If
    Next i
    
    MsgBox "Rows expanded successfully!", vbInformation
End Sub

How to Use This Code #

1. Open your Excel file
2. Press Alt + F11 to open the VBA Editor
3. Insert a new module: Right-click your workbook in the Project Explorer → Insert → Module
4. Paste the code above into the module
5. Press F5 to run the macro, or assign it to a button for easier access

Customization Note #

The code includes two options for calculating rows to insert:

• Standard logic: Uncomment the first rowsToInsert line to get exactly Quantity rows per entry (1 row for Quantity=1, 3 rows for Quantity=3, etc.)
• Your example logic: The default line matches your expected output, where Quantity=1 results in 2 rows, and all higher quantities result in their stated number of rows.

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