|
Private Sub CommandButton1_Click()
Dim k As Integer
Set nd = Worksheets("新款ND柜")
Set bzjb = Worksheets("标准件表")
k1_nd = nd.Range("A65536").End(xlUp).Row '表1的行数
k2_nd = nd.Cells(4, 2000).End(1).Column '表1的列数
'表2的行数
K1_bzjb = bzjb.Range("A65536").End(xlUp).Row
'表2的列数
k2_bzjb = bzjb.Cells(2, 2000).End(1).Column
nd.Range("A" & k1_nd + 1) = TextBox1.Value
nd.Range("B" & k1_nd + 1) = TextBox2.Value
nd.Range("C" & k1_nd + 1) = TextBox3.Value
nd.Range("D" & k1_nd + 1) = TextBox4.Value
nd.Range("E" & k1_nd + 1) = TextBox5.Value
If TextBox2.Value = "" And TextBox3.Value = "" Then
ElseIf TextBox3.Value = "kd" Then
kk1 = TextBox2.Value
kk2 = TextBox3.Value
For n = 2 To K1_bzjb
a1 = bzjb.Cells(n, 1)
a2 = bzjb.Cells(n, 2)
a2 = Str(a2)
a2 = LTrim(a2)
If a1 = kk2 And a2 = kk1 Then
GoTo 跳出
End If
Next n
跳出:
For n1 = 7 To k2_nd Step 2
If nd.Cells(1, n1) <> "" Then
b1 = nd.Cells(1, n1)
End If
a1 = nd.Cells(4, n1) + b1
For n2 = 3 To k2_bzjb
a2 = bzjb.Cells(n, n2)
If a1 = a2 Then
a3 = bzjb.Cells(n + 1, n2)
nd.Cells(k1_nd + 1, n1 + 1) = TextBox5.Value * a3
nd.Cells(k1_nd + 1, n1 + 1).Interior.ColorIndex = 4
'ElseIf nd.Cells(k1_nd, n2).Value <> "" Then Interior.ColorIndex = 4
GoTo tu
End If
Next n2
tu:
Next n1
Else
End If
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox1.SetFocus
End Sub
上面的代码只是首要判断是不是“KD”值才运行下面的代码,但我相要有其它条件也可以运行代码的方法,
求改代码
本帖最后由 hwc2ycy 于 2012-10-12 15:18 编辑
- Option Explicit
- Dim k As Integer
- Dim nd As Worksheet
- Dim bzjb As Worksheet
- Dim k1_nd As Long
- Dim k2_nd As Long
- Dim K1_bzjb As Long
- Dim k2_bzjb As Long
- Private Sub CommandButton1_Click()
- 'Dim k As Integer
- Set nd = Worksheets("新款ND柜")
- Set bzjb = Worksheets("标准件表")
- k1_nd = nd.Range("A65536").End(xlUp).Row '表1的行数
- k2_nd = nd.Cells(4, 2000).End(1).Column '表1的列数
- '表2的行数
- K1_bzjb = bzjb.Range("A65536").End(xlUp).Row
- '表2的列数
- k2_bzjb = bzjb.Cells(2, 2000).End(1).Column
- nd.Range("A" & k1_nd + 1) = TextBox1.Value
- nd.Range("B" & k1_nd + 1) = TextBox2.Value
- nd.Range("C" & k1_nd + 1) = textbox3.Value
- nd.Range("D" & k1_nd + 1) = TextBox4.Value
- nd.Range("E" & k1_nd + 1) = TextBox5.Value
- If TextBox2.Value = "" And textbox3.Value = "" Then Exit Sub
-
- If textbox3.Value = "kd" Then
- 子过程
- Else
- If textbox3.Value = "kb" Then 子过程
- End If
-
- TextBox1.Value = ""
- TextBox2.Value = ""
- textbox3.Value = ""
- TextBox4.Value = ""
- TextBox5.Value = ""
- TextBox1.SetFocus
- End Sub
- Sub 子过程()
- kk1 = TextBox2.Value
- kk2 = textbox3.Value
- For n = 2 To K1_bzjb
- a1 = bzjb.Cells(n, 1)
- a2 = bzjb.Cells(n, 2)
- a2 = Str(a2)
- a2 = LTrim(a2)
- If a1 = kk2 And a2 = kk1 Then
- GoTo 跳出
- End If
- Next n
- 跳出:
- For n1 = 7 To k2_nd Step 2
- If nd.Cells(1, n1) <> "" Then
- b1 = nd.Cells(1, n1)
- End If
- a1 = nd.Cells(4, n1) + b1
- For n2 = 3 To k2_bzjb
- a2 = bzjb.Cells(n, n2)
- If a1 = a2 Then
- a3 = bzjb.Cells(n + 1, n2)
- nd.Cells(k1_nd + 1, n1 + 1) = TextBox5.Value * a3
- nd.Cells(k1_nd + 1, n1 + 1).Interior.ColorIndex = 4
- 'ElseIf nd.Cells(k1_nd, n2).Value <> "" Then Interior.ColorIndex = 4
- GoTo tu
- End If
- Next n2
- tu:
- Next n1
- End Sub
复制代码
|
|