On a sheet there are numerous cells containing month names written out (e.g.
June, December, etc.). I need to convert these to their ordinal values (e.g.
January = 1; June = 6, etc.).

Can anyone post some example code that easily converts these short of having
to do a case select with 12 cases?

Thanks much in advance.

edit <find and replace<replace all

12 times


--
Greetings from New Zealand
Bill K
"quartz" wrote in message
...
On a sheet there are numerous cells containing month names written out
(e.g.
June, December, etc.). I need to convert these to their ordinal values
(e.g.
January = 1; June = 6, etc.).

Can anyone post some example code that easily converts these short of
having
to do a case select with 12 cases?

Thanks much in advance.

Sub Trial()
ActiveCell = NoMonth("June")
End Sub

Function NoMonth(MonthName As String) As Byte
NoMonth = Month(CDate(MonthName & "/1/2005"))
End Function

--
Sincerilly, thanks a lot.

Oscar Picos
Office Autom


"quartz" wrote:

On a sheet there are numerous cells containing month names written out (e.g.
June, December, etc.). I need to convert these to their ordinal values (e.g.
January = 1; June = 6, etc.).

Can anyone post some example code that easily converts these short of having
to do a case select with 12 cases?

Thanks much in advance.

Quartz,

You need to complete the list of month's where noted below, but this seems
to work. It operates on any cells a selection that have month names in
them:

For Each cell In Selection
Select Case LCase(cell.Value)
Case "january", "february", "march" 'take it from here, I'm too lazy
For i = 1 To 12
If Month(CDate(cell.Value & " " & "1, 2005")) = Month(CDate(i &
" " & "1, 2005")) Then
cell.Value = Month(cell.Value & " " & "1, 2005")
End If
Next i
End Select
Next cell
End Sub

hth,

Doug
"quartz" wrote in message
...
On a sheet there are numerous cells containing month names written out
(e.g.
June, December, etc.). I need to convert these to their ordinal values
(e.g.
January = 1; June = 6, etc.).

Can anyone post some example code that easily converts these short of
having
to do a case select with 12 cases?

Thanks much in advance.

Try the following code:

Sub alpha()

' Declare the variables
Dim rngCell As Range
Dim strMonthName As String
Dim intMonthNumber As Integer

' Investigate the worksheet entries
For Each rngCell In ActiveSheet.UsedRange

' Initalize the variables
strMonthName = rngCell.Value
intMonthNumber = Month(DateValue(strMonthName & " 1, 1969"))

' Rewrite cell value if a month
If intMonthNumber = 1 And intMonthNumber <= 12 Then _
rngCell.Value = intMonthNumber

Next rngCell
End Sub

Regards,

Alasdair Stirling

"quartz" wrote:

On a sheet there are numerous cells containing month names written out (e.g.
June, December, etc.). I need to convert these to their ordinal values (e.g.
January = 1; June = 6, etc.).

Can anyone post some example code that easily converts these short of having
to do a case select with 12 cases?

Thanks much in advance.