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.

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

Southern at Heart wrote:

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

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.
--
Gary''s Student - gsnu200826


"Southern at Heart" wrote:

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.

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

--
Rick (MVP - Excel)


"Southern at Heart" wrote in
message ...
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.

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