What VBA function to use for?

Hi,

I have sells which contains states codes, such as NY, CA, IA, etc., and
would like to find those cells containing these codes. I was wondering if
there is a VBA function to test whether the text in a given cell contains,
let say "NY". I just could not find it.

Thanks.

Look at Find in VBA Help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GreenInIowa" <GreenInIowa@discussions.microsoft.com> wrote in message
news:81D597FE-4CD4-43C3-9EC3-8BE48FF5A2F6@microsoft.com...
> Hi,
>
> I have sells which contains states codes, such as NY, CA, IA, etc., and
> would like to find those cells containing these codes. I was wondering if
> there is a VBA function to test whether the text in a given cell contains,
> let say "NY". I just could not find it.
>
> Thanks.

One way:

If ActiveCell.Value = "NY" Then
'Do something
End If

In article <81D597FE-4CD4-43C3-9EC3-8BE48FF5A2F6@microsoft.com>,
"GreenInIowa" <GreenInIowa@discussions.microsoft.com> wrote:

> Hi,
>
> I have sells which contains states codes, such as NY, CA, IA, etc., and
> would like to find those cells containing these codes. I was wondering if
> there is a VBA function to test whether the text in a given cell contains,
> let say "NY". I just could not find it.
>
> Thanks.

Find works great for searching a range of cells for Text strings. Seach this
forum for Find and FindNext code. There are piles of it. If you want to test
a single cell then InStr is probably the function that you want. You can also
use Like... Depends on what exactly you want to do.
--
HTH...

Jim Thomlinson


"GreenInIowa" wrote:

> Hi,
>
> I have sells which contains states codes, such as NY, CA, IA, etc., and
> would like to find those cells containing these codes. I was wondering if
> there is a VBA function to test whether the text in a given cell contains,
> let say "NY". I just could not find it.
>
> Thanks.

Actually, the cell contains more than "NY" or "CA". One example: The cell has
this text "Cherokee County, IA, Employment (NAICS), Government (Thousands)
" and I would like to be able to test whether this cell contains "IA" word.
The "FIND" function that you are suggesting in VBA appears to work if the
cell only has one text. What happens if you a several other words in the same
cell.

Thanks.



"Jim Thomlinson" wrote:

> Find works great for searching a range of cells for Text strings. Seach this
> forum for Find and FindNext code. There are piles of it. If you want to test
> a single cell then InStr is probably the function that you want. You can also
> use Like... Depends on what exactly you want to do.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "GreenInIowa" wrote:
>
> > Hi,
> >
> > I have sells which contains states codes, such as NY, CA, IA, etc., and
> > would like to find those cells containing these codes. I was wondering if
> > there is a VBA function to test whether the text in a given cell contains,
> > let say "NY". I just could not find it.
> >
> > Thanks.

The find function has an argument Lookat which can take the value xlWhole or
xlPart

Lookat:=xlWhole
or
Lookat:=xlPart

the xlPart does what you want.

Dim rng as Range
set rng = Cells.Find(What:="IA",Lookin:=xlValues,Lookat:=xlPart)
if not rng is nothing then
msgbox "found at " & rng.Address
else
msgbox "Not found"
End if

It has other arguments as well:
Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase,
MatchByte

--
Regards,
Tom Ogilvy


"GreenInIowa" <GreenInIowa@discussions.microsoft.com> wrote in message
news:F660FE1E-EA25-4AD1-974F-7B8023AF3652@microsoft.com...
> Actually, the cell contains more than "NY" or "CA". One example: The cell
has
> this text "Cherokee County, IA, Employment (NAICS), Government (Thousands)
> " and I would like to be able to test whether this cell contains "IA"
word.
> The "FIND" function that you are suggesting in VBA appears to work if the
> cell only has one text. What happens if you a several other words in the
same
> cell.
>
> Thanks.
>
>
>
> "Jim Thomlinson" wrote:
>
> > Find works great for searching a range of cells for Text strings. Seach
this
> > forum for Find and FindNext code. There are piles of it. If you want to
test
> > a single cell then InStr is probably the function that you want. You can
also
> > use Like... Depends on what exactly you want to do.
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "GreenInIowa" wrote:
> >
> > > Hi,
> > >
> > > I have sells which contains states codes, such as NY, CA, IA, etc.,
and
> > > would like to find those cells containing these codes. I was wondering
if
> > > there is a VBA function to test whether the text in a given cell
contains,
> > > let say "NY". I just could not find it.
> > >
> > > Thanks.

Your suggestions were very useful. Thank you very much.

GreenInIowa

"Tom Ogilvy" wrote:

> The find function has an argument Lookat which can take the value xlWhole or
> xlPart
>
> Lookat:=xlWhole
> or
> Lookat:=xlPart
>
> the xlPart does what you want.
>
> Dim rng as Range
> set rng = Cells.Find(What:="IA",Lookin:=xlValues,Lookat:=xlPart)
> if not rng is nothing then
> msgbox "found at " & rng.Address
> else
> msgbox "Not found"
> End if
>
> It has other arguments as well:
> Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase,
> MatchByte
>
> --
> Regards,
> Tom Ogilvy
>
>
> "GreenInIowa" <GreenInIowa@discussions.microsoft.com> wrote in message
> news:F660FE1E-EA25-4AD1-974F-7B8023AF3652@microsoft.com...
> > Actually, the cell contains more than "NY" or "CA". One example: The cell
> has
> > this text "Cherokee County, IA, Employment (NAICS), Government (Thousands)
> > " and I would like to be able to test whether this cell contains "IA"
> word.
> > The "FIND" function that you are suggesting in VBA appears to work if the
> > cell only has one text. What happens if you a several other words in the
> same
> > cell.
> >
> > Thanks.
> >
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > Find works great for searching a range of cells for Text strings. Seach
> this
> > > forum for Find and FindNext code. There are piles of it. If you want to
> test
> > > a single cell then InStr is probably the function that you want. You can
> also
> > > use Like... Depends on what exactly you want to do.
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "GreenInIowa" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have sells which contains states codes, such as NY, CA, IA, etc.,
> and
> > > > would like to find those cells containing these codes. I was wondering
> if
> > > > there is a VBA function to test whether the text in a given cell
> contains,
> > > > let say "NY". I just could not find it.
> > > >
> > > > Thanks.
>
>
>