|
本人写了段小程序,用到的主要功能就是查询+返回,程序运行时通过扫描内容自动输出查找内容,返回内容格式必须与EXECL文件中的格式一样,有的字母上带上划线,下划线等,目前代码无法运行,请大神查看之~
Private Sub CommandButton1_Click()
Dim rng As Range, a, b, c
a = TextBox1.Text
b = TextBox2.Text
c = TextBox3.Text
d = TextBox4.Text
With Worksheets("数据源")
.Range("a2").Value = a
If TextBox4.Text = "" Or TextBox5.Text = "" Or TextBox6.Text = "" Then
MsgBox "设备机台号不能为空"
Else
.Range("D7").Value = b
.Range("D8").Value = c
.Range("D9").Value = d
TextBox5.Text = .Range("D1").Value
TextBox6.Text = .Range("D2").Value
TextBox7.Text = .Range("E5").Value
TextBox8.Text = .Range("E6").Value
TextBox9.Text = .Range("H3").Value
TextBox10.Text = .Range("E7").Value
TextBox11.Text = .Range("E8").Value
TextBox12.Text = .Range("E9").Value
If Target.Columns.Count = 1 And Target.Column = 4 And Target.Row >= 2 Then
Set rng = Range("F13:F" & Range("F" & Rows.Count).End(xlUp).Row).Find(Target, , xlValues, xlWhole)
If Not rng Is Nothing Then
rng.Offset(0, 1).Copy Target.Offset(0, 1)
Else
Target.Offset(0, 1).Clear
End If
If Target.Columns.Count = 1 And Target.Column = 4 And Target.Row >= 5 Then
Set rng = Range("N10:N" & Range("N" & Rows.Count).End(xlUp).Row).Find(Target, , xlValues, xlWhole)
If Not rng Is Nothing Then
rng.Offset(0, 1).Copy Target.Offset(0, 1)
Else
Target.Offset(0, 1).Clear
End If
If Target.Columns.Count = 1 And Target.Column = 4 And Target.Row >= 6 Then
Set rng = Range("R13:R" & Range("R" & Rows.Count).End(xlUp).Row).Find(Target, , xlValues, xlWhole)
If Not rng Is Nothing Then
rng.Offset(0, 1).Copy Target.Offset(0, 1)
Else
Target.Offset(0, 1).Clear
End If
If Target.Columns.Count = 1 And Target.Column = 4 And Target.Row >= 7 Then
Set rng = Range("V13:V" & Range("V" & Rows.Count).End(xlUp).Row).Find(Target, , xlValues, xlWhole)
If Not rng Is Nothing Then
rng.Offset(0, 1).Copy Target.Offset(0, 1)
Else
Target.Offset(0, 1).Clear
End If
If Target.Columns.Count = 1 And Target.Column = 4 And Target.Row >= 8 Then
Set rng = Range("V13:V" & Range("V" & Rows.Count).End(xlUp).Row).Find(Target, , xlValues, xlWhole)
If Not rng Is Nothing Then
rng.Offset(0, 1).Copy Target.Offset(0, 1)
Else
Target.Offset(0, 1).Clear
End If
If Target.Columns.Count = 1 And Target.Column = 4 And Target.Row >= 9 Then
Set rng = Range("V13:V" & Range("V" & Rows.Count).End(xlUp).Row).Find(Target, , xlValues, xlWhole)
If Not rng Is Nothing Then
rng.Offset(0, 1).Copy Target.Offset(0, 1)
Else
Target.Offset(0, 1).Clear
End If
End If
End If
End If
End If
End If
End With
End Sub
Private Sub CommandButton1_Click()
Dim rng As Range, a, b, c
a = TextBox1.Text
b = TextBox2.Text
c = TextBox3.Text
d = TextBox4.Text
With Worksheets("数据源")
a.Range("a2").Value = a
If TextBox4.Text = "" Or TextBox5.Text = "" Or TextBox6.Text = "" Then
MsgBox "设备机台号不能为空"
Else
.Range("D7").Value = b
.Range("D8").Value = c
.Range("D9").Value = d
TextBox5.Text = .Range("D1").Value
TextBox6.Text = .Range("D2").Value
TextBox7.Text = .Range("E5").Value
TextBox8.Text = .Range("E6").Value
TextBox9.Text = .Range("H3").Value
TextBox10.Text = .Range("E7").Value
TextBox11.Text = .Range("E8").Value
TextBox12.Text = .Range("E9").Value
arr = Array("", "", "F", "F", "F", "N", "R", "V", "V", "V") 'D列2--9行所对应要查找的列号
For i = 2 To 9
x = .Cells(i, 4) '查找的内容
xx = arr(i) '在XX列中查找
Set rng = .Range(.Cells(13, xx), .Cells(13, xx).End(xlDown)).Find(x, , xlValues, xlWhole)
If Not rng Is Nothing Then
rng.Offset(0, 1).Copy .Cells(i, 5)
Else
.Cells(i, 5).Clear
End If
Next
End If
End With
End Sub
|
|