Advanced partial search box

[Jmoz092]

I have a similar question as posed in this thread:

Advanced search

about this code:

[COLOR=#454545][FONT=&quot][SIZE=2]Sub searchVal()[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    Application.ScreenUpdating = False[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    Dim foundVal As Range[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    Dim sAddr As String[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    Dim response As String[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    response = InputBox("Please enter the value to search.")[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    With ActiveSheet.UsedRange[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]        Set foundVal = .Find(response, LookIn:=xlValues, lookat:=xlWhole)[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]        If Not foundVal Is Nothing Then[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            sAddr = foundVal.Address[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            Do[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]                foundVal.EntireRow.Interior.ColorIndex = 3[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]                Set foundVal = .FindNext(foundVal)[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            Loop While foundVal.Address <> sAddr[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            sAddr = ""[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]        Else[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]            MsgBox ("Value not found.  Please try again.")[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]        End If[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    End With[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    Set foundVal = Nothing[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]    Application.ScreenUpdating = True[/SIZE][/FONT][/COLOR]
[SIZE=2][/SIZE][COLOR=#454545][FONT=&quot][SIZE=2]End Sub[/SIZE][/FONT][/COLOR]

How can I use this to search on a hidden sheet and have the user's selection autofill into the next available empty cell in a range on the user's active sheet? I'd like for a partial search to initiate a response in the search box.

For example:
user searches for "app"
search box gives list of responses (that contain "app" anywhere in the full string) that will decrease as the input string lengthens and becomes more specific.

 

[dmt32]

Hi,
try this update to the code & see if does what you want

Sub searchVal()
    Dim foundVal As Range
    Dim firstaddress As String, Search As String
    Dim wsSearch As Worksheet
    
    Set wsSearch = ThisWorkbook.Worksheets("Sheet1")
    
    Do
    Search = InputBox("Please enter the value to search.", "Search")
'cancel pressed
    If StrPtr(Search) = 0 Then Exit Sub
    Loop Until Len(Search) > 0
    
    With wsSearch.UsedRange
        Set foundVal = .Find(Search, LookIn:=xlValues, lookat:=xlPart)
        If Not foundVal Is Nothing Then
            firstaddress = foundVal.Address
            Do
                ActiveSheet.Cells(Cells(Rows.Count, "A").End(xlUp).Row, 1).Offset(1, 0).Value = foundVal.Value
                Set foundVal = .FindNext(foundVal)
            Loop While foundVal.Address <> firstaddress
        Else
            MsgBox Search & Chr(10) & "Value not found.  Please try again.", 48, "Not Found"
        End If
    End With
    Set foundVal = Nothing
End Sub

Change the sheet name you are searching (shown in RED) as required.

Dave

 

[Jmoz092]

Thanks, but that did not work. I got an error that said "Subscript out of range". I tried to code it so the sheet to be searched was visible and then not, but still no luck...still got a subscript out of range error.

Sub searchVal()
    Dim foundVal As Range
    Dim firstaddress As String, Search As String
    Dim wsSearch As Worksheet
    
[COLOR=#ff0000]    sheets("Sheet 2").Visible = True[/COLOR]
    Set wsSearch = ThisWorkbook.Worksheets("Sheet 2")
    
    Do
    Search = InputBox("Please enter the value to search.", "Search")
'cancel pressed
    If StrPtr(Search) = 0 Then Exit Sub
    Loop Until Len(Search) > 0
    
    With wsSearch.UsedRange
        Set foundVal = .Find(Search, LookIn:=xlValues, lookat:=xlPart)
        If Not foundVal Is Nothing Then
            firstaddress = foundVal.Address
            Do
                ActiveSheet.Cells(Cells(Rows.Count, "A").End(xlUp).Row, 1).Offset(1, 0).Value = foundVal.Value
                Set foundVal = .FindNext(foundVal)
            Loop While foundVal.Address <> firstaddress
        Else
            MsgBox Search & Chr(10) & "Value not found.  Please try again.", 48, "Not Found"
        End If
    End With
    Set foundVal = Nothing
[COLOR=#ff0000]    sheets("Sheet 2").Visible = False[/COLOR]
End Sub

Also, and this is a really rookie question, which part of the code is telling it where to type your search results/selection onto the active sheet? I want to put the search result/selection into the first blank cell in a range, say from A1:A7...but won't know in advance if any of those cells are blank. Thanks

 

[Jmoz092]

We've "frankensteined" some code together but we're not getting the end result (for the user) that we intended. We can search the column of data that we're interested in on the hidden sheet (sheet2,G1:G463). If the search box input is found within the data in the hidden sheet's column, then the search's input data will be transposed to the range of intended cells if blank (active sheet B12:b16), but the rest of the string (on sheet 2 G1:G463) associated with the search box's input is not transposed as intended. Also, if the search box's input data is not in the search filed on sheet 2, the code hangs up and freezes the worksheet.

How can we get the whole data string on sheet 2 to transpose into the intended cells on the active sheet?
Why is the message box "Value not found, please try again." not popping up if the search data is not contained within our search field?

Here is the code:

Sub search2()
    Application.ScreenUpdating = False
    
    Dim foundVal As Range
    Dim sAddr As String
    Dim response As String
    
    response = InputBox("Please enter the value to search.")
    
    With Sheet2.Range("G1:G463")
        Set foundVal = .Find(response, LookIn:=xlValues, lookat:=xlPart)
        If Not foundVal Is Nothing Then
            sAddr = foundVal.Address
            Do
            If WorksheetFunction.CountA(Range("B12:B16")) = 5 Then
                Range("B12").End(xlDown).Offset(1).Select
                MsgBox "All data slots full"
            ElseIf Len(Range("B12")) = 0 Then
                Range("B12").Value = foundVal.Value
                
            ElseIf Len(Range("B13")) = 0 Then
                 Range("B13").Value = foundVal.Value
                 
            Else
            Range("B12").End(xlDown).Offset(1).Select
            
        End If
            
            
                Set foundVal = .FindNext(foundVal)
            Loop While foundVal.Address <> sAddr
        Else
            MsgBox response & Chr(10) & "Value not found.  Please try again.", 48, "Not Found"
        End If
    End With
    
    Set foundVal = Nothing
    
    Application.ScreenUpdating = True
    
End Sub