A列满足条件的记录相同的有很多,如何在另1个工作表输入A列的条件后,把所有A列与所输入条件相符的记录全部列示在新的工作表上
Sub 查询()
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=65536
Selection.ClearContents
Range("J1").Select
Selection.Copy
Sheets("价格表").Select
ActiveWindow.SmallScroll Down:=-12
Selection.AutoFilter Field:=1, Criteria1:="J1", Operator:=xlAnd
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("查询").Select
Range("A1").Select
ActiveSheet.Paste
Range("I4").Select
End Sub
我录的宏,我把Criteria1:="J1",里的J1改成实际的条件可以出来,但是J1我不确认会是什么,这里的错误在哪里
2ãçéæ¡ä»¶åï¼å¦çéAåå
å«âå京âç
3ãç¹ âå
å«...âï¼å¨å
å«é项éå¡«åâå京â
4ãçéç»æ为ï¼
5ãéä¸å·²çéçåºåï¼ç¹âæ¥æ¾åéæ©...â-âå®ä½æ¡ä»¶...â
5ãå®ä½æ¡ä»¶éâå¯è§åå
â
6ãå âå¤å¶â
7ãå°éè¦çå°æ¹ï¼å¦Sheet2ç²è´´å³å¯
这样的效果?
你用高级筛选就做到你的需求了。
如果你一定要用VBA,可以用单元格事件完成,
以下代码在查询表的A1输入你的条件,就自动得到查询的结果(代码粘贴到查询表对应的编辑栏中)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim arr, brr(), x&, y&, i&, str1$
If Target.Address = "$A$1" Then
str1 = Target.Value
arr = Sheets("价格表").UsedRange
For x = 1 To UBound(arr)
If arr(x, 1) = str1 Then
i = i + 1
ReDim Preserve brr(1 To UBound(arr, 2), 1 To i)
For y = 1 To UBound(arr, 2)
brr(y, i) = arr(x, y)
Next y
End If
Next x
Range("A3").Resize(65533, 255).ClearContents
Range("A3").Resize(UBound(brr, 2), UBound(brr)) = Application.Transpose(brr)
End If
End Sub本回答被网友采纳