本帖最后由 jds 于 2016-11-7 11:12 编辑
谢谢老师,帮忙修改下 “修改成入库的C列,在C列任何一单元格输入编码调取数据库内容”
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$C$1" And Target.Address <> "$D$1" Then Exit Sub
Dim Myr&, Arr, i&, n&
Myr = Sheet2.[c65536].End(xlUp).Row
Arr = Sheet2.Range("c8:K" & Myr): n = 3: m = 3
Range("a4:k1000").Clear
For i = 1 To UBound(Arr)
If Target.Address = "$C$1" Then
If Arr(i, 1) >= Target.Value And Arr(i, 1) <= Target.Offset(0, 1).Value Then
m = m + 1
Cells(m, 3) = Arr(i, 1)
Cells(m, 2) = Arr(i, 2)
Cells(m, 4) = Arr(i, 3)
Cells(m, 6) = Arr(i, 8)
Cells(m, 7) = Arr(i, 6)
End If
Else
End If
Next
Cells(4, 1).Resize(m - 3, 11).Borders.LineStyle = 1
End Sub
本帖最后由 fjmxwrs 于 2016-11-5 18:40 编辑
对应的哪些内容,你的入库表和数据库表中的项目不同呀,如果代码中对应的不对,你自己改一下对应的列即可
- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim myr%, myc%, arr, mybh
- myr = Target.Row
- myc = Target.Column
- If myr > 3 And myc = 3 Then
- arr = Sheet1.UsedRange
- mybh = Target.Value
- For x = 8 To UBound(arr)
- If arr(x, 3) = mybh Then
- Application.EnableEvents = False
- Cells(myr, 2) = arr(x, 4)
- Cells(myr, 4) = arr(x, 1)
- Cells(myr, 5) = arr(x, 28)
- Cells(myr, 6) = arr(x, 10)
- Cells(myr, 7) = arr(x, 8)
- Application.EnableEvents = True
- Exit Sub
- End If
- Next x
- MsgBox "没有找到该编码!"
- End If
- End Sub
复制代码
|