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.

What VBA function to use for?
 

Look at Find in VBA Help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"GreenInIowa" wrote in message
...
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.

What VBA function to use for?
 

One way:

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

In article ,
"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.

What VBA function to use for?
 

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.

What VBA function to use for?
 

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.

What VBA function to use for?
 

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:=xlP art)
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" wrote in message
...
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.

What VBA function to use for?
 

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:=xlP art)
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" wrote in message
...
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.