执行时循环以获取 SKU 编号

Question

我正在尝试自动化我的 SKU 编号。我有 3 列。第一列有28，第二列有6，最后第三列有58。

我希望 SKU 具有这样的趋势0{(###)col1}{(##)col2}{(##)col3}0

我的代码如下所示

Sub SKU()
    Dim x As Long
    x = 1
    i = 1
    j = 1
    k = 1
    Do While Cells(i, 1) <> ""
        Do While Cells(j, 2) <> ""
            Do While Cells(k, 3) <> ""
                Cells(x, 4).Value = Format(0, "0") & Format(i, "000") & _
                Format(j, "00") & Format(k, "00") & Format(0, "0")
                k = k + 1
                x = x + 1
            Loop
            j = j + 1
        Loop
        i = i + 1
    Loop
End Sub

Answer 1

无需使用 Do 循环。找到最后一行，然后使用 For 循环。

这是你正在尝试的吗？

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long
    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet2")
    With ws
        '~~> Find last row
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = 1 To lRow
            If .Cells(i, 1) <> "" And .Cells(i, 2) <> "" And .Cells(i, 3) <> "" Then
                '0{(###)col1}{(##)col2}{(##)col3}0
                .Cells(i, 4).Value = "'0" & _
                                     Format(.Cells(i, 1), "000") & _
                                     Format(.Cells(i, 2), "00") & _
                                     Format(.Cells(i, 3), "00") & _
                                     "0"
            End If
        Next i
    End With
End Sub

28,6,58的输出002806580

Answer 2

正如我在问题的评论中提到的，删除第一个和第二个循环do-while然后替换：

Cells(x, 4).Value = Format(0, "0") & Format(i, "000") & _
           Format(j, "00") & Format(k, "00") & Format(0, "0")

跟：

Cells(k, 4) = "'" & Format(Cells(k, 1), "000") & _
           Format(Cells(k, 2), "00") & Format(Cells(k, 3), "00")

结果：0280658

如果要添加前导零和结束零：

Cells(k, 4) = "'0" & Format(Cells(k, 1), "000") & _
           Format(Cells(k, 2), "00") & Format(Cells(k, 3), "00") & "0"

结果：002806580