Retaining multiple entries in Inputbox

Question

So below is my code. I have a selection of data on a sheet, that the code will go to. Based on the first userinput, the data will filter for that data in the column, if the user wants to add in another piece of data that is to be filtered out, i have build in a prompt to select a second piece of data.

What i want to do, but don't know how , is to loop through asking the user if they want to keep adding in more filter data, whilst retaining any previously selected/entered data. any thoughts/ideas would be greatly appreciated.

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

You already know you need to add a loop. This, in combination with the fact that the filter can take an array, provides a potential solution.

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

The loop takes the user input and adds it to an array. Once the loop is exited, the array is applied as the criteria where each element of the array is OR'ed.

Answer 2

If I understand correctly, you want the previous input each time to populate the new input box (if the user has clicked yes).

Note: I have not tested the following but this will give you the idea of how to structure your logic.

To do this you can utilize the Default property of the [Inputbox()][1] method.

Like so:

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

OR

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

As for looping a [For...Next][2] loop should do the trick, where the end part will be the count of your criteria (perhaps Range.Count).
Something like:

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

Although it might seem excessive using the Range.Count property (as in this example that evaluates to 5592) this ensures the user has the opportunity to search every single cell in the search range. The Exit For ensures the loop stops if the user clicks the No button on the MsgBox.