Looking for a Month Name in a String

[wutzke]

Within my worksheet is a number of cells that MIGHT contain a name of
a Month

white mothistled January holdew February tracour December necters
exally slopers
tracing sentagen March samputs twistwased June unent chama puls
moperveyeberannochas micracadwatextilitiously
hung flamons cons bradritabol cashortimizes red

Is there a way programmically to loop thru each cell and search for
"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct",
"Nov", Dec"

 

[Don Guillett]

A bit more detail of sample data and do you want to look at a lot of cells
for any month or just a specific month? Give before/after examples so we
fully understand what you desire the first time

 

[Dave Peterson]

You can loop through all the cells with something like:

Option Explicit
Sub testme()

Dim myRng As Range
Dim wks As Worksheet
Dim mCtr As Long
Dim myCell As Range
Dim myStr As String

Set wks = Worksheets("Sheet1")
Set myRng = Nothing
On Error Resume Next
Set myRng = wks.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No text cells found!"
Exit Sub
End If

For Each myCell In myRng.Cells
For mCtr = 1 To 12
myStr = Format(DateSerial(2008, mCtr, 1), "mmm")
'xl2002(?) or higher
myStr = MonthName(Month:=mCtr, abbreviate:=True)
If InStr(1, myCell.Value, myStr, vbTextCompare) > 0 Then
'do what
MsgBox "Found one: " & myCell.Address & "--" & myStr
'stop looking in that cell?
Exit For
End If
Next mCtr
Next myCell

End Sub

=======
But looping through the cells isn't always the quickest way to get things done.
I don't know what you're doing, but you may want to consider using Find/FindNext
to search for the month abbreviations.

 

[Dana DeLouis]

Not any better, but another variation along this theme...

Mths = Application.GetCustomListContents(3)

For Each myCell In myRng.Cells
For Mth = 1 To 12
If InStr(1, myCell.Value, Mths(Mth), vbTextCompare) > 0 Then
'etc...