在 Excel 中使用 VBA 搜索多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10455044/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Searching for multiple values using VBA in Excel
提问by NCC
I do not know if it is possible to implement this problem in VBA or it must be done with VB. Net using Visual Studio.
我不知道是否可以在 VBA 中实现这个问题，或者必须用 VB 来完成。网络使用 Visual Studio。
Problem: Excel has its search function and it is a pain if there many value available or you must find a value that far away from column A.
问题：Excel 有它的搜索功能，如果有很多可用的值或者你必须找到一个离 A 列很远的值会很痛苦。
I would like to have something like this
我想要这样的东西
In which I can specify what columns I want to display by their header name. Rearrange the column in the way I would like to, and ability to copy and paste. Like datagrid in Visual basic? Is it possible?
我可以在其中指定要通过标题名称显示的列。按照我想要的方式重新排列列，以及复制和粘贴的能力。像 Visual Basic 中的 datagrid 吗？是否可以？
回答by Siddharth Rout
I have given below both the methods - VBA and VB.net (take your pick) :)
我在下面给出了这两种方法 - VBA 和 VB.net（任你选择）:)
USING VB.NET
使用 VB.NET
Place a DataGridView on your VB.net Form and also place a Button. Place this code in the Button
在您的 VB.net 窗体上放置一个 DataGridView 并放置一个按钮。将此代码放在按钮中
Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim masterTable As New DataTable
Dim cnnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=""Excel 12.0 Xml;HDR=NO"";Data Source=""{0}"";"
Using da As New OleDb.OleDbDataAdapter("select * from [Sheet1$] Where F1 = 'Test1'", String.Format(cnnStr, "C:\Book1.xlsx"))
da.Fill (masterTable)
End Using
DataGridView1.DataSource = masterTable
End Sub
End Class
And you are done :)
你完成了:)
SNAPSHOT
快照
I am using limited Data.
我正在使用有限的数据。
You can also change your string "select * from [Sheet1$] Where F1 = 'Test1'"to "select F1 as Name,F2 as PN, F3 as [Inventory Loc] from [Sheet1$] Where F1 = 'Test1'"to display the headers as below
您还可以将字符串更改"select * from [Sheet1$] Where F1 = 'Test1'"为"select F1 as Name,F2 as PN, F3 as [Inventory Loc] from [Sheet1$] Where F1 = 'Test1'"以显示如下标题
EDIT
编辑
In case you are wondering how to do it in VBA
如果您想知道如何在 VBA 中执行此操作
USING VBA
使用 VBA
Place a Listbox and a Command Button on a Form and then use this code.
在窗体上放置一个列表框和一个命令按钮，然后使用此代码。
Option Explicit
Private Sub CommandButton1_Click()
Dim ws As Worksheet, ws1 As Worksheet
Dim rng As Range
Dim lastRow As Long
Dim Ar As Variant
Set ws = Sheets("Sheet1")
lastRow = ws.Cells.Find(What:="*", After:=ws.Range("A1"), _
Lookat:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Set rng = ws.Range("A1:C" & lastRow)
Set ws1 = Sheets.Add
With rng
ws.AutoFilterMode = False
.AutoFilter Field:=1, Criteria1:="Test1"
.SpecialCells(xlCellTypeVisible).Copy ws1.Range("A1")
ws.AutoFilterMode = False
lastRow = ws1.Cells.Find(What:="*", After:=ws1.Range("A1"), _
Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row
Ar = ws1.Range("A1:C" & lastRow)
Application.DisplayAlerts = False
ws1.Delete
Application.DisplayAlerts = True
End With
With Me.ListBox1
.Clear
.ColumnHeads = False
.ColumnCount = 3
.List = Ar
.ColumnWidths = "50;50;50"
.TopIndex = 0
End With
End Sub
SNAPSHOT
快照
MORE FOLLOWUP
更多跟进
Hi Siddharth, thank you very much for both code. For VB. Net it is wonderful. For the VBA in Exel, is there any way that I can copy (using Ctrl + C) to copy the data - single row will be fine, although copy multiple rows is more desirable. I am able to replace "Test1" with textbox i49.tinypic.com/2ceq3yf.jpg – user1370854 5 hours ago
嗨 Siddharth，非常感谢您提供这两个代码。对于 VB。净它是美妙的。对于 Exel 中的 VBA，有什么方法可以复制（使用 Ctrl + C）来复制数据 - 单行就可以了，尽管复制多行更可取。我可以用文本框 i49.tinypic.com/2ceq3yf.jpg 替换“Test1” – user1370854 5 小时前
Yes it is possible to copy the single selected or multiple selected items from the listbox to cliboard. To make the listobx multiselect, in the design mode, set the property of the listbox to fmMultiSelectMulti1. Next Add a command button and paste this code.
是的，可以将单个选定或多个选定项目从列表框中复制到剪贴板。要使 listobx 多选，在设计模式下，将列表框的属性设置为fmMultiSelectMulti1。接下来添加一个命令按钮并粘贴此代码。
This code is again based on the data I use above so amend it as applicable. When you press the Copybutton, the data is copied to the clipboard and then you can simply use CTL Vto paste the data where ever you want; for example in Notepad.
此代码再次基于我在上面使用的数据，因此请根据需要对其进行修改。当您按下Copy按钮时，数据被复制到剪贴板，然后您可以简单地CTL V将数据粘贴到您想要的位置；例如在记事本中。
Private Sub CommandButton2_Click()
Dim MyData As DataObject
Dim i As Long
Dim strCopiedText As String
Set MyData = New DataObject
With Me.ListBox1
For i = 1 To .ListCount
If .Selected(i - 1) Then
strCopiedText = strCopiedText & _
.List(i - 1, 0) & vbTab & _
.List(i - 1, 1) & vbTab & _
.List(i - 1, 2) & vbCrLf
End If
Next i
If Len(strCopiedText) > 0 Then
MyData.Clear
MyData.SetText strCopiedText
MyData.PutInClipboard
MsgBox "Data copied to clipboard"
End If
End With
End Sub
