|
少了:
End With
End If
Private Sub CommandButton8_Click() '生成编码
Application.ScreenUpdating = False
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)
Dim Newarr
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")
Newrow = Sheets("数据库").Range("A65536").End(xlUp).Row
If Newrow >= 2 Then
ReDim Newarr(2 To Newrow)
For i = 2 To Newrow
Newarr(i) = Val(Mid(Sheets("数据库").Range("A" & i).Text, 4, 3))
Next i
G = Format(Application.WorksheetFunction.Max(Newarr) + 1, "000")
Else
With Sheets("数据库")
Set Rngb = .Range("B2", "B" & Newrow).Find(what:=TextBox1.Text, LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlNext)
If Not Rngb Is Nothing Then
Windows("编码库.xls").Visible = False
y = Val(Mid(Sheets("数据库").Range("A" & i).Text, 4, 3))
If y = 1 Then Val (Mid(Sheets("数据库").Range("A" & i).Text, 4, 3))
If y = 2 Then Exit Sub
Exit Sub
Else
G = "001"
End If
Arr(3) = G
TextBox4.Text = Join(Arr, "")
TextBox4.Enabled = False
TextBox4.BackColor = &H80000003
End With
Windows("编码库.xls").Visible = False
End If
End With
End If
Application.ScreenUpdating = True
End Sub |
|