Excel的VBA自动化 - 复制行英寸×＆QUOT;根据单元格值的次数 [英] 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.

我们需要打印条码codeS为我们所有的库存，这包括4000变种每一个具体的数量。

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.

我们使用戴莫为我们吧code打印硬件/软件。 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?

下面是数据的样本：

<一个href=\"https://www.evernote.com/shard/s187/sh/b0d5b92a-c5f6-469c-92fb-3d4e03d97544/d176d3448ba0cafbf3d61506402d9e8b/res/254447d2-486d-454f-8871-a0962f03253d/skitch.png\" rel=\"nofollow\">https://www.evernote.com/shard/s187/sh/b0d5b92a-c5f6-469c-92fb-3d4e03d97544/d176d3448ba0cafbf3d61506402d9e8b/res/254447d2-486d-454f-8871-a0962f03253d/skitch.png

• 如果列N = 0，忽略并移动到下一行


• 如果列N> 1，复制当前行，N次数（到一个单独的表）

我试图找到谁做了类似的事情，这样我可以修改code，但搜索的一个小时后，我仍然是正确的，我开始了。预先感谢您的帮助！

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

本code的告诫是，可以在数量列中没有空字段。我用ð可以随意替代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自动化 - 复制行英寸×＆QUOT;根据单元格值的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！