|
求大仙们帮我看看这段代码的错误在哪里??
本来要达到的效果是,在录入时,如果名称重复,对应行的指定字符,现在提示编译错误!!!!求助啊!
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
Application.ScreenUpdating = True
End Sub
本帖最后由 zjdh 于 2012-7-15 08:59 编辑
在你的基础上修改:
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:B" & W)
For I = 1 To UBound(Brr)
If LSH < Val(Mid(Brr(I, 1), 4, 3)) Then LSH = Val(Mid(Brr(I, 1), 4, 3))
If Brr(I, 2) = TextBox1.Text 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
|
|