|
本帖最后由 一片薄云 于 2016-6-12 09:37 编辑
请求老师帮助:工作表“查询”a1单元格输入日期完成后 (比如按回车键或选择其他单元格),自动在a2、b2单元格填写表1、表2,并将填写的日期在工作表“表1”、“表2”中搜索,搜索到的单元格下面内容(9行)分别复制到工作表“查询”的a2、b2下面。
同样在其他单元格比如e1、f1输入也要有同样效果。
放在查询表时间中,只能实现输入日期后双击日期出结果,Worksheet_Change事件好像做不到! - Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
- Dim rng1 As Range, arr(), brr()
- Set rng = Target
- With Target
- If .Row = 1 And Target <> "" Then
- Set rng1 = Sheet1.Cells.Find(What:=.Value, LookIn:=xlValues, LookAt:=xlWhole)
- arr = rng1.Offset(1, 0).Resize(9, 1)
- .Offset(1, 0) = "表1"
- .Offset(2, 0).Resize(9, 1) = arr
- Set rng1 = Sheet2.Cells.Find(What:=.Value, LookIn:=xlValues, LookAt:=xlWhole)
- brr = rng1.Offset(1, 0).Resize(9, 1)
- .Offset(1, 1) = "表2"
- .Offset(2, 1).Resize(9, 1) = brr
- End If
- End With
- Cancel = True
- End Sub
复制代码
|
|