simple code that does a search for me and returns a string

[Southern at Heart]

I've been recording macros to figure how to do what I need to do, but can't
quite get it...

I need my code to search column A, and find the cell that contains (in it's
value) the string strText. strText will just be part of it's value, but
there will only be one cell in column A that matches.
I need this asigned to a new string, strFound
thanks, much.

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim strText as string
Dim strFound as string
Dim FoundCell as range

strText = "sometexthere"

with worksheets("Sheet9999999") '<-- change the sheet name here
with .range("a:a")
set foundcell = .cells.Find(What:=strtext, _
After:=.cells(.cells.count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
end with
end with

if foundcell is nothing then
msgbox "Not found"
else
strFound = foundcell.value
end if
end sub

 

[Gary''s Student]

We will search for "happiness"

Sub findit()
strText = "happiness"
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
v = Cells(i, 1).Value
If InStr(v, strText) > 0 Then
strFound = v
End If
Next
MsgBox (strFound)
End Sub

The entire sentence containing "happiness" gets output.

 

[Rick Rothstein]

Another possibility (using Gary''s Student's search word "happiness")...

Sub FindMe()
Dim strText As String, strFound As String
strText = "happiness"
On Error Resume Next
strFound = WorksheetFunction.Index(Range("A:A"), WorksheetFunction. _
Match("*" & strText & "*", Range("A:A"), 0))
MsgBox strFound
End Sub

 

[Southern at Heart]

thanks. I ended up with:

Function findit(strText As String) As String

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
v = Cells(i, 1).Value
If InStr(v, strText) > 0 Then
strFound = v
End If
Next
findit = strFound
End Function