Excel VBA 自动化 - 复制行“x"基于单元格值的次数 [英] Excel VBA automation - copy row "x" number of times based on cell value

问题描述

我正在尝试以一种方式使 Excel 自动化，从而为我节省无数小时的乏味数据输入.这是我的问题.

I'm attempting to automate Excel in a way that will save me countless hours of tedious data entry. Here's my problem.

我们需要为所有库存打印条码，其中包括 4,000 种不同的变体，每个变体都有特定数量.

We need to print barcodes for all of our inventory, which includes 4,000 variants each with a specific quantity.

Shopify 是我们的电子商务平台，不支持定制出口；但是，可以导出所有变体的 CSV，其中包括库存计数列.

Shopify is our e-commerce platform and they do not support customized exports; however, can export a CSV of all variants, which includes an inventory count column.

我们将 Dymo 用于条码打印硬件/软件.Dymo 每行只会打印一个标签(它会忽略数量列).

We use Dymo for our barcode printing hardware/software. Dymo will only print one label per row (it ignores the quantity column).

有没有办法让excel根据库存列中的值自动复制x"行的次数?

Is there a way to automate excel to duplicate the row "x" number of times based on the value in the inventory column?

以下是数据示例:

https://www.evernote.com/shard/s187/sh/b0d5b92a-c5f6-469c-92fb-3d4e03d97544/d176d3448ba0cafbf3d61506402d61506402d92506402/shard/s187/sh/b0d5b92a-c5f6-469c-92fb-2png

• 如果第 N 列 = 0，则忽略并移至下一行
• 如果第 N 列 > 1，则复制当前行N"次(到单独的工作表)

我试图找到做过类似事情的人，以便我可以修改代码，但经过一个小时的搜索，我仍然是我开始的地方.预先感谢您的帮助！

I tried to find someone who had done something similar so that I could modify the code, but after an hour of searching I'm still right where I started. Thank you in advance for the help!

推荐答案

大卫打败了我，但另一种方法从未伤害任何人.

David beat me to it but an alternate approach never hurt anyone.

考虑以下数据

Item           Cost Code         Quantity
Fiddlesticks   0.8  22251554787  0
Woozles        1.96 54645641     3
Jarbles        200  158484       4
Yerzegerztits  56.7 494681818    1

有这个功能

Public Sub CopyData()
    ' This routing will copy rows based on the quantity to a new sheet.
    Dim rngSinglecell As Range
    Dim rngQuantityCells As Range
    Dim intCount As Integer

    ' Set this for the range where the Quantity column exists. This works only if there are no empty cells
    Set rngQuantityCells = Range("D1", Range("D1").End(xlDown))

    For Each rngSinglecell In rngQuantityCells
        ' Check if this cell actually contains a number
        If IsNumeric(rngSinglecell.Value) Then
            ' Check if the number is greater than 0
            If rngSinglecell.Value > 0 Then
                ' Copy this row as many times as .value
                For intCount = 1 To rngSinglecell.Value
                    ' Copy the row into the next emtpy row in sheet2
                    Range(rngSinglecell.Address).EntireRow.Copy Destination:= Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)                                
                    ' The above line finds the next empty row.

                Next
            End If
        End If
    Next
End Sub

在 sheet2 上产生以下输出

Produces the following output on sheet2

Item            Cost    Code        Quantity
Woozles         1.96    54645641    3
Woozles         1.96    54645641    3
Woozles         1.96    54645641    3
Jarbles         200     158484      4
Jarbles         200     158484      4
Jarbles         200     158484      4
Jarbles         200     158484      4
Yerzegerztits   56.7    494681818   1

此代码的警告是数量列中不能有空字段.我用了 D，所以你可以用 N 代替你的情况.

The caveats with this code is that there can be no empty fields in the Quantity column. I used D so feel free to substitute N for your case.

这篇关于Excel VBA 自动化 - 复制行“x"基于单元格值的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！