|
各位大神,求助,在做临时存储区域的数据查询后不会动态变化,只是显示第一行。而且数据查询效率特别慢,在work 的change事件中。帮忙改改。
Private Sub Worksheet_Change(ByVal Target As Range) '当表格发生变化时,以i16编号变化 加载对应信息
On Error Resume Next '如果运行错误则忽略
Set selectsheet = Application.ThisWorkbook.Worksheets("信息查询界面") '设置工作表
If Target = selectsheet.Range("i16") Then
If selectsheet.Range("i16") <> 0 Then
selectsheet.Range("C18") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 2, 0)
selectsheet.Range("C19") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 3, 0)
selectsheet.Range("C20") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 4, 0)
selectsheet.Range("C21") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 5, 0)
selectsheet.Range("C22") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 6, 0)
selectsheet.Range("C23") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 7, 0)
selectsheet.Range("E18") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 8, 0)
selectsheet.Range("E19") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 9, 0)
selectsheet.Range("E20") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 10, 0)
selectsheet.Range("E21") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 11, 0)
selectsheet.Range("E22") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 12, 0)
selectsheet.Range("E23") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 13, 0)
selectsheet.Range("G18") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 14, 0)
selectsheet.Range("G19") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 15, 0)
selectsheet.Range("G20") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 16, 0)
selectsheet.Range("G21") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 17, 0)
selectsheet.Range("G22") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 18, 0)
selectsheet.Range("I18") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 19, 0)
selectsheet.Range("I19") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 20, 0)
selectsheet.Range("I20") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 21, 0)
selectsheet.Range("I21") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 22, 0)
selectsheet.Range("I22") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 23, 0)
selectsheet.Range("I23") = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), 24, 0)
Else
selectsheet.Range("C18:C23,E18:E23,G18:G22,i18:i23") = ""
End If
End If
END sub
试试
- Private Sub Worksheet_Change(ByVal Target As Range)
- Set selectsheet = Application.ThisWorkbook.Worksheets("信息查询界面") '设置工作表
- m = 1
- If Target = selectsheet.Range("i16") Then
- If selectsheet.Range("i16") <> 0 Then
- For i = 3 To 9 Step 2
- For j = 18 To 23
- m = m + 1
- selectsheet.Cells(j, i) = Application.WorksheetFunction.VLookup(selectsheet.Range("i16"), selectsheet.Range("A38:Y10000"), m, 0)
- Next j
- Next i
- End If
- Else
- selectsheet.Range("C18:C23,E18:E23,G18:G22,i18:i23") = ""
- End If
- End Sub
复制代码
|
-
翻页界面
|