Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "$B$6" Then Dim x& x = Workbooks("定额库").Sheets("" & Range("b6").Value & "").Range("a65536").End(xlUp).Row Range("C6").Resize(x - 1, 2) = Workbooks("定额库").Sheets("" & Range("b6").Value & "").Range("a2:b" & x).Value End If Application.ScreenUpdating = True End Sub
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 3 Or Target.Column <> 3 Or Target = "" Or Cells(Target.Row, 2) = "" Then Exit Sub On Error GoTo 100 Application.ScreenUpdating = False Dim x%, MyXL As Object, y$ Set MyXL = GetObject("" & ThisWorkbook.Path & "\定额库.xls" & "") y = Cells(Target.Row, 2) x = MyXL.Sheets("" & y & "").Range("a:a").Find(Cells(Target.Row, 3).Value).Row Range("d" & Target.Row) = MyXL.Sheets("" & y & "").Range("b" & x).Value Set MyXL = Nothing 100: Application.ScreenUpdating = True End Sub