Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
month to number
Tim,
Can you stand one more solution? You should be able to paste the following into a module, select the cells with the names and run the Sub. '--------------------------------------------------- Option Base 1 'This is required at the top of the module. 'Called by Sub ProvideTheMonth Function GetMonthIndex(ByRef Mth As String) As Long Dim N As Long Dim ShortMonth As Variant ShortMonth = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _ "Jul", "Aug", "Sep", "Oct", "Nov", "Dec") For N = 1 To 12 If ShortMonth(N) = Mth Then GetMonthIndex = N Exit Function End If Next GetMonthIndex = 0 End Function 'The month index is added to Column AF in the same row as the name. 'Upper case, lower case and full month names will work. 'Column AF is column 32 'Calls Function GetMonthIndex Sub ProvideTheMonth() Dim i As Long Dim MthRng As Range Dim Mth As Range Set MthRng = Selection For Each Mth In MthRng i = GetMonthIndex(Left$(WorksheetFunction.Proper(Mth.V alue), 3)) If i = 0 Then Cells(Mth.Row, 32).Value = "Check month name" Else Cells(Mth.Row, 32).Value = i End If Next Set Mth = Nothing Set MthRng = Nothing End Sub '------------------------------------------------------ Regards, Jim Cone San Francisco, CA '******************** Hi, Can you show me how to write a macro to convert a three place month abreviation, Jan, Feb, Mar, etc., in column Y into a number, 1, 2, 3, etc., in column AF? Thank You. Regards, Tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number of days in month counted from shortened name of month & yea | Excel Worksheet Functions | |||
Converting Month Number to Month Text Abbreviation | Excel Worksheet Functions | |||
Function or formula to convert "text" month to number of month? | Excel Discussion (Misc queries) | |||
show month number as month name in Excel? | Excel Discussion (Misc queries) | |||
How to extract month number from month name | Excel Discussion (Misc queries) |