Saving value of Search String

[phiero21]

Is it possible to save the text of the search string in a particular cell using VBA/Macro.

So, if a user searches for "Bed", the text within A1 should change to "Bed"

 

[Trebor76]

Hi phiero21,

Try this while on the sheet with the text to be found:

Option Explicit
Sub Macro1()

    Dim strText As String
    Dim rngFoundCell As Range

    strText = InputBox("Please enter the text to be searched:", "Text Search")
   
    If Len(strText) = 0 Then Exit Sub 'Either nothing was entered or <Cancel> was pressed
   
    Set rngFoundCell = Cells.Find(What:=strText, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
   
    Range("A1").Value = strText
   
    If rngFoundCell Is Nothing Then
        MsgBox "There were no cells in the sheet containing the text """ & strText & """.", vbExclamation, "Text Search"
    End If
   
End Sub

Regards,

Robert