Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Adding a custom function to the default excel function list | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |