|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
凑合看吧
Private Sub Worksheet_Change(ByVal Target As Range)'私有Worksheet_Change事件,Target As Range
Dim brr(1 To 9999, 1 To 11)'申明二维数组
Application.ScreenUpdating = False'关闭屏幕刷新
If Target.Address(0, 0) = "F1" Then'假如选中单元格[F1]
If Len(Target) = 0 Then'假如为空
Exit Sub'退出
Else'否则
arr = Sheet2.UsedRange'定义数组arr
For i = 5 To UBound(arr)'使i从5到aar数组末位
If arr(i, 5) = Target Then'假如arr(i,5)=选中单元格值
m = m + 1'm=m+1
For j = 1 To 11'使j从1到11
brr(m, j) = arr(i, j + 1)'数组brr(m, j) 赋值为 arr(i, j + 1)
Next'下一个j
End If'结束if arr(i, 5) = Target Then
Next'下一个i
If m Then Range("a7:k" & Rows.Count).ClearContents: [a7].Resize(m, 11) = brr'目标区域赋值
End If'结束If Len(Target) = 0 Then
End If'结束If Target.Address(0, 0) = "F1" Then
Application.ScreenUpdating = True'启用屏幕刷新
End Sub'结束 |
|