如何实现运用VBA进行批量查询？
0
我想实现在worksheet的A列粘贴很多行数据，然后在B列和C列出现对应的内容，但是因为代码是worksheets_change事件，不能够批量查询，所以想问一下大家如何改写代码实现该功能，代码如下：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim firstaddress As String
Dim ws As Worksheet
If Target.Column = 1 Then
For Each ws In ThisWorkbook.Worksheets
With ws.UsedRange
If ws.Name <> "Summary" Then
Set rng = .Cells.Find(what:=Target.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not rng Is Nothing Then
If Target <> "" Then
Target.Offset(0, 1) = .Cells(1, rng.Column)
Target.Offset(0, 2) = rng.Offset(0, 1)
Else
Target.Offset(0, 1) = ""
Target.Offset(0, 2) = ""
End If
End If
End If
End With
Next
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim firstaddress As String
Dim ws As Worksheet
If Target.Column = 1 Then
For Each ws In ThisWorkbook.Worksheets
With ws.UsedRange
If ws.Name <> "Summary" Then
Set rng = .Cells.Find(what:=Target.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not rng Is Nothing Then
If Target <> "" Then
Target.Offset(0, 1) = .Cells(1, rng.Column)
Target.Offset(0, 2) = rng.Offset(0, 1)
Else
Target.Offset(0, 1) = ""
Target.Offset(0, 2) = ""
End If
End If
End If
End With
Next
End If
End Sub
没有找到相关结果
重要提示：提问者不能发表回复，可以通过评论与回答者沟通，沟通后可以通过编辑功能完善问题描述，以便后续其他人能够更容易理解问题.
0 个回复