在输入框中保留多个条目

Question

下面是我的代码。 我在一张纸上选择了数据，代码将转到这些数据。根据第一个用户输入，数据将在列中筛选该数据，如果用户想要添加另一条要过滤掉的数据，我已经在提示中构建了选择第二条数据。

我想做的，但不知道如何做的，是循环询问用户是否要继续添加更多过滤器数据，同时保留任何以前选择/输入的数据。 任何想法/想法将不胜感激。

Sub Macro4()
Dim strUserInput As String
Dim strUserInput1 As Integer
Dim strUserInputX As String
Dim strUserInput2 As String
strUserInput = MsgBox("Would fund would you like to search for?: ")
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
Range("C5").Select
ActiveSheet.Range("$A$1:$H$699").AutoFilter Field:=3, Criteria1:=strUserInput
strUserInput1 = MsgBox("Would you like to add another fund to your selection?: ", vbYesNo) 'i want to loop through multiple selections to add more criteria in the filter if the answer is yes
If strUserInput1 = vbYes Then
strUserInput2 = InputBox("What other fund would you like to add to your selection?: ")
ActiveSheet.Range("$A$1:$H$699").AutoFilter Field:=3, Criteria1:=strUserInput, _
Operator:=xlOr, Criteria2:=strUserInput2
End If
MsgBox ("Your selections are complete")

End Sub

Answer 1

您已经知道需要添加一个循环。 这与滤波器可以采用数组的事实相结合，提供了一种潜在的解决方案。

Public Sub Macro4()
Dim strUserInput As String
Dim res As VbMsgBoxResult
Dim crit() As String
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
Range("C5").Select
strUserInput = InputBox("What fund would you like to search for?: ")
ReDim crit(0)
crit(UBound(crit)) = strUserInput
res = vbYes
Do While res = vbYes
res = MsgBox("Would you like to add another fund to your selection?: ", vbYesNo)
If res = vbYes Then
strUserInput = InputBox("What other fund would you like to add to your selection?: ")
ReDim Preserve crit(UBound(crit) + 1)
crit(UBound(crit)) = strUserInput
End If
Loop
ActiveSheet.Range("$A$1:$H$699").AutoFilter Field:=3, Criteria1:=crit
MsgBox "Your selections are complete"
End Sub

循环接受用户输入并将其添加到数组中。 退出循环后，数组将作为条件应用，其中数组的每个元素都是 OR 的。

Answer 2

如果我理解正确，您希望每次以前的输入都填充新的输入框(如果用户单击了"是"(。

注意：我没有测试以下内容，但这将为您提供如何构建逻辑的想法。

为此，您可以利用[Inputbox()][1]方法的Default属性。

这样：

strUserInput2 = InputBox("What other fund would you like to add to your selection?: ", ,strUserInput) 

或

strUserInput2 = InputBox(Prompt:="What other fund would you like to add to your selection?: ", Default:=strUserInput)

至于循环[For...Next][2]循环应该可以解决问题，其中结束部分将是您的标准计数(也许是Range.Count(。
像这样：

Dim LoopCounter as Long
'Your 1st InputBox goes here
For LoopCounter = 1 to ActiveSheet.Range("$A$1:$H$699").Count
'Your MsgBox code goes here
If strUserInput1 = vbNo Then
Exit For
Else
'Your 2nd InputBox goes here
End If
Next LoopCounter

尽管使用Range.Count属性似乎有些过分(如本例中的计算结果为5592(，但这可确保用户有机会搜索搜索范围中的每个单元格。Exit For可确保在用户单击MsgBox上的"否"按钮时循环停止。