Finding a string within a cell value

[peacelittleone]

Say I have a string = "abc".
Say I have a cell value = "123 abc 456 def"

In VB how do I findout if the cell value contains my string?

Right now I am trying this: (which of course doesn't work)

Set Found = Find(c.Value, req.Value, 1)

Where c is my string and req is the cell value I want to look in.

TIA!!!!

Heather

 

[Jim Cone]

Heather,

The "InStr" Function does what you want.
It returns a Variant (Long) specifying the position of the first occurrence
of one string within another.

Jim Cone
San Francisco, USA


"peacelittleone"
<[email protected]>
wrote in message
Say I have a string = "abc".
Say I have a cell value = "123 abc 456 def"
In VB how do I findout if the cell value contains my string?
Right now I am trying this: (which of course doesn't work)
Set Found = Find(c.Value, req.Value, 1)
Where c is my string and req is the cell value I want to look in.
TIA!!!!
Heather.--
peacelittleone

 

[Richard Buttrey]

Say I have a string = "abc".
Say I have a cell value = "123 abc 456 def"

In VB how do I findout if the cell value contains my string?

Right now I am trying this: (which of course doesn't work)

Set Found = Find(c.Value, req.Value, 1)

Where c is my string and req is the cell value I want to look in.

TIA!!!!

Heather.

If InStr(value, c) Then MsgBox "Found"

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Bernie Deitrick]

Heather,

Sub TryNow()
Dim Found As Integer
Dim c As Range
Dim strFind As String

strFind = "abc"
Set c = ActiveCell
Found = InStr(1, c.Value, strFind)
If Found = 0 Then
MsgBox "Not Found"
Else
MsgBox "Found starting at position " & Found
End If
End Sub

HTH,
Bernie
MS Excel MVP

 

[Nigel]

use instr function to find the string......

instr will return zero if not found or a number > 0 showing the start of the
search string

eg ... search in range A1 for string abc, the 1 at the beginning refers to
the starting position of the string you are searching...

If instr(1,Range("A1"),"abc") > 0
MsgBox "Found It!"
Else
MsgBox "Not Found"
End If

instr is case sensitive so use UCase to convert strings if this is not
important

--
Cheers
Nigel



"peacelittleone"

 

[Bob Phillips]

myVar = Instr(1, Range("A1").Value, "abc")
if myVar > 0 Then MsgBox "found"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"peacelittleone"

 

[Zack Barresse]

Well, just to give another version of what you could use ...

Option Explicit
Sub FindTheWhiteRabbit()
If c.Text Like "*abc*" Then MsgBox "Found in string."
End Sub

... but the InStr function (as previously mentioned) works just as well.


--
Regards,
Zack Barresse, aka firefytr


"peacelittleone"