Search for pattern of numbers/letters and wildcard

[HalfricanHokie]

I would like to search for a certain pattern that appears in a cell. For example, I have certain cells that begin with a 7 digit project code (Ex: "1234567 - Project Red"). I was planning to use an IF inside a FOR loop and then do an action when I find the cell with the pattern. I thought I remember it being something like Format(########*) so that I have the wildcard on the end since there is more than just the numbers.

If someone knows how to do this I would really appreciate it! Also, if you are familiar with how to make other patterns work such as Number-Letter-Number or Capital-Lowercase-Capital, that would be great to know!

Thanks!

 

[Rick Rothstein]

I would like to search for a certain pattern that appears in a cell. For example, I have certain cells that begin with a 7 digit project code (Ex: "1234567 - Project Red"). I was planning to use an IF inside a FOR loop and then do an action when I find the cell with the pattern. I thought I remember it being something like Format(########*) so that I have the wildcard on the end since there is more than just the numbers.

If someone knows how to do this I would really appreciate it! Also, if you are familiar with how to make other patterns work such as Number-Letter-Number or Capital-Lowercase-Capital, that would be great to know!

What you want to use is the Like operator, something like this for your example..

If {CellContent} Like "#######*" The

where CellContent is whatever method you now use to retrieve the value in the cell you are examining. As for the second part of your question, look up the Like operator in the VBA help files (put the text cursor next to or inside of the work Like in your code and press F1). Also, once you learn how to form patterns for the Like operator, you may find the code I posted in my mini-blog article here to be of use or interest...

<!-- title / author block -->Find a text substring that matches a given "pattern"

 

[HalfricanHokie]

Perfect! Exactly what I was looking for, and the article is also quite useful. Thanks again!