返回符合条件的项目列表的 VBA 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24276035/
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
VBA Macro that returns a list of items that meet criteria
提问by Brady
I am trying to create a UserForm in Excel 2010/2013 that will look through a list of items and return a complete list based on a number I provide.
我正在尝试在 Excel 2010/2013 中创建一个用户窗体，它将查看项目列表并根据我提供的数字返回完整列表。
Here's what the list would look like: See Example(image hosted on Imgur)
列表如下所示：参见示例（图片托管在 Imgur 上）
A Here's a snippit in case the image won't load...
A 这是一个片段，以防图像无法加载...
Location ----- Title ----- Days Past
A2: 0001 | B2: Movie 1 | C2: 32
A3: 0001 | B3: Movie 2 | C3: 18
A4: 0001 | B4: Movie 3 | C4: 10
A5: 0004 | B5: Movie 1 | C5: 32
A6: 0007 | B6: Movie 1 | C6: 32
A7: 0007 | B7: Movie 2 | C7: 18
A8: 0009 | B8: Movie 1 | C8: 32
A9: 0014 | B9: Movie 1 | C9: 32
位置 ----- 标题 ----- 过去的天数
A2: 0001 | B2：电影1 | C2：32
A3：0001 | B3：电影2 | C3：18
A4：0001 | B4：电影3 | C4：10
A5：0004 | B5：电影1 | C5：32
A6：0007 | B6：电影1 | C6：32
A7：0007 | B7：电影2 | C7：18
A8：0009 | B8：电影1 | C8：32
A9：0014 | B9：电影1 | C9：32
I have a userform that will return the first item in the list, but not the complete list. Ideally I would like to stay away from using a list box, mainly because the goal is to be able to copy the items in the full list.
我有一个用户表单，它将返回列表中的第一项，而不是完整的列表。理想情况下，我不想使用列表框，主要是因为目标是能够复制完整列表中的项目。
I have tried the Index() formula but I don't know how to transfer that to work in VBA.
Any help you have would be great!
我已经尝试过 Index() 公式，但我不知道如何将其转移到 VBA 中工作。
你有任何帮助都会很棒！
回答by Moiety Design
I have written this for you, which if your location values are given in the Acolumn, Titles in the Band Days Past in the Cthis should work:
我已经为你写了这个，如果你的位置值在A列中给出，标题B和过去的日子C应该可以工作：
Private Sub SUBMITBUTTON_Click()
Dim counter As Integer, TITLELIST(), DAYSPAST(), fullString As String
fullString = ""
If LOCATIONTEXTBOX.Text = "" Then
MsgBox "Please input a location"
Exit Sub
End If
For Each Cell In ActiveSheet.UsedRange.Cells
If Cell.Value = LOCATIONTEXTBOX.Text Then
counter = counter + 1
End If
Next
ReDim TITLELIST(counter)
ReDim DAYSPAST(counter)
counter = 0
For i = 1 To Cells(1, 1).End(xlDown).Row
If Cells(1, i).Value = LOCATIONTEXTBOX.Text Then
TITLELIST(counter) = Cells(i, 2).Value
DAYSPAST(counter) = Cells(i, 3).Value
fullString = fullString & CStr(TITLELIST(counter)) & "," & CStr(DAYSPAST(counter)) & ","
counter = counter + 1
End If
Next
MsgBox fullString
Range("H8").Value = fullString
End Sub
If you change the names of SUBMITBUTTON and LOCATIONTEXTBOX then it should work in your userform.
如果您更改 SUBMITBUTTON 和 LOCATIONTEXTBOX 的名称，那么它应该可以在您的用户表单中使用。
