[已解决]求助！！！关于按产品类型生成流水号

aj1314520

上一期在zjdh大哥的帮助下，使我原本一团糟的自动进行产品料号编码的程序能够达到预期的效果！！！在此感谢！！！

因上级的指令，需要对此程序再进行一个改良，本人菜鸟，无良策！！！特来求助！！！
改良内容:流水号生成与产品类型相关联，因为产品类型有很多种，要求如下：所有的产品类型初始都是从001开始递增，单独的流水号，如USB-A型公头的流水号从001开始，USB -A型母头的流水号也 要从001开始，互不干涉，后续添加对应的产品类型，产品流水号自动累加，当产品名称出现重复时，自动截取上次录入的相关流水号为此次的流水号！！！！
求助！！望大仙来帮帮忙！！！
以下是附件！
编码库.rar (45.08 KB, 下载次数: 29)

2012-7-15 10:18 上传

点击文件名下载附件

最佳答案

月排行榜 / 总排行榜

zjdh

2012-7-15 10:51

Private Sub CommandButton8_Click()    '生成编码
    If ComboBox1cd.Value = "" Or ComboBox2xlfn.Value = "" _
       Or ComboBox3xz.Value = "" Or ComboBox5xnfn.Value = "" Or ComboBox6pppai.Value = "" Or ComboBox4cdxn.Value = "" Then
        MsgBox "【编码选择信息】为必填项，请输入完整！"
        Exit Sub
    Else
        Dim Arr(1 To 7)
        Windows("编码库.xls").Visible = True
        With Sheets("参数")
            Arr(1) = Format(Application.WorksheetFunction.VLookup(ComboBox1cd.Value, .Range("A2", "B" & EndrowA), 2, 0), "0")
            Arr(2) = Format(Application.WorksheetFunction.VLookup(ComboBox2xlfn.Value, .Range("D2", "E" & EndrowD), 2, 0), "00")
            Arr(4) = Format(Application.WorksheetFunction.VLookup(ComboBox5xnfn.Value, .Range("J2", "K" & EndrowJ), 2, 0), "00")
            Arr(5) = Format(Application.WorksheetFunction.VLookup(ComboBox6pppai.Value, .Range("M2", "N" & EndrowM), 2, 0), "00")
            Arr(6) = Format(Application.WorksheetFunction.VLookup(ComboBox4cdxn.Value, .Range("P2", "Q" & EndrowP), 2, 0), "00")
            Arr(7) = Format(Application.WorksheetFunction.VLookup(ComboBox3xz.Value, .Range("S2", "T" & EndrowS), 2, 0), "0")
        End With
        W = Sheets("数据库").Range("A65536").End(3).Row
        If W = 1 Then LSH = 1: GoTo 10
        Brr = Sheets("数据库").Range("A2:F" & W)
        For I = 1 To UBound(Brr)
            If Brr(I, 6) = ComboBox2xlfn.Value And LSH < Val(Mid(Brr(I, 1), 4, 3)) Then LSH = Val(Mid(Brr(I, 1), 4, 3))
            If Brr(I, 2) & Brr(I, 6) = TextBox1.Text & ComboBox2xlfn.Value Then LSH = Val(Mid(Brr(I, 1), 4, 3)): GoTo 10
        Next
        LSH = LSH + 1
10      Arr(3) = Format(LSH, "000")
        TextBox4.Text = Join(Arr, "")
        TextBox4.Enabled = False
        TextBox4.BackColor = &H80000003
    End If
    Windows("编码库.xls").Visible = False
End Sub

跳转到最佳答案楼层

zjdh

Private Sub CommandButton8_Click()    '生成编码
    If ComboBox1cd.Value = "" Or ComboBox2xlfn.Value = "" _
       Or ComboBox3xz.Value = "" Or ComboBox5xnfn.Value = "" Or ComboBox6pppai.Value = "" Or ComboBox4cdxn.Value = "" Then
        MsgBox "【编码选择信息】为必填项，请输入完整！"
        Exit Sub
    Else
        Dim Arr(1 To 7)
        Windows("编码库.xls").Visible = True
        With Sheets("参数")
            Arr(1) = Format(Application.WorksheetFunction.VLookup(ComboBox1cd.Value, .Range("A2", "B" & EndrowA), 2, 0), "0")
            Arr(2) = Format(Application.WorksheetFunction.VLookup(ComboBox2xlfn.Value, .Range("D2", "E" & EndrowD), 2, 0), "00")
            Arr(4) = Format(Application.WorksheetFunction.VLookup(ComboBox5xnfn.Value, .Range("J2", "K" & EndrowJ), 2, 0), "00")
            Arr(5) = Format(Application.WorksheetFunction.VLookup(ComboBox6pppai.Value, .Range("M2", "N" & EndrowM), 2, 0), "00")
            Arr(6) = Format(Application.WorksheetFunction.VLookup(ComboBox4cdxn.Value, .Range("P2", "Q" & EndrowP), 2, 0), "00")
            Arr(7) = Format(Application.WorksheetFunction.VLookup(ComboBox3xz.Value, .Range("S2", "T" & EndrowS), 2, 0), "0")
        End With
        W = Sheets("数据库").Range("A65536").End(3).Row
        If W = 1 Then LSH = 1: GoTo 10
        Brr = Sheets("数据库").Range("A2:F" & W)
        For I = 1 To UBound(Brr)
            If Brr(I, 6) = ComboBox2xlfn.Value And LSH < Val(Mid(Brr(I, 1), 4, 3)) Then LSH = Val(Mid(Brr(I, 1), 4, 3))
            If Brr(I, 2) & Brr(I, 6) = TextBox1.Text & ComboBox2xlfn.Value Then LSH = Val(Mid(Brr(I, 1), 4, 3)): GoTo 10
        Next
        LSH = LSH + 1
10      Arr(3) = Format(LSH, "000")
        TextBox4.Text = Join(Arr, "")
        TextBox4.Enabled = False
        TextBox4.BackColor = &H80000003
    End If
    Windows("编码库.xls").Visible = False
End Sub

ztccsj

zjdh 发表于 2012-7-15 10:51
Private Sub CommandButton8_Click()    '生成编码
    If ComboBox1cd.Value = "" Or ComboBox2xlfn.Valu ...

拥有知识就是不一样，佩服

zjdh

编码及输入.rar (41.52 KB, 下载次数: 45)

2012-7-15 13:21 上传

点击文件名下载附件

aj1314520

zjdh 发表于 2012-7-15 13:21

非常感谢，成功实现了我需要的功能，佩服啊！！！