Convert "Month" to 1, 2, 3...12

[Guest]

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.

 

[Bill Kuunders]

edit <>find and replace<>replace all

12 times

 

[Guest]

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

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

 

[Doug Glancy]

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

 

[Guest]

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