If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
i want to create a formula to change a months name
I want to create a formula to allow me to enter a # into a cell that will display a corresponding months name in that cell. In doing an IF statement I have been getting a cricular error, I assume because i'm creating the formula IN the refrence cell, or Excel can't find the project or library. Either way I'm sure I'm not creating the formula correctly.
Example: I want to enter 01 for January, 02 for February and have the corresponding mont displayed in the cell I'm creating the formula in. Is this possibe if written in the same cell? Sounds pretty simple but I can't get an "IF" statement to work for me. It's been a loooong time since I last worked with Excel so I'd appreciate any help you can give. BTW: my office is 2002 Pro. |
#2
|
|||
|
|||
i want to create a formula to change a months name
You can either have a value or a function in a cell, not both. When you
enter a value in the cell, the formula is overwritten. You can use an event macro to change the value on entry. Put this in the worksheet code module (right click on the worksheet tab and choose view code): Private Sub Worksheet_Change(ByVal rCell As Excel.Range) With rCell If .Count 1 Then Exit Sub If .Address(False, False) = "A1" Then _ If IsNumeric(.Value) Then _ .Value = Format(DateSerial(2004, .Value, 1), "mmmm") End With End Sub Change A1 to your desired cell. In article , "change a month" wrote: I want to create a formula to allow me to enter a # into a cell that will display a corresponding months name in that cell. In doing an IF statement I have been getting a cricular error, I assume because i'm creating the formula IN the refrence cell, or Excel can't find the project or library. Either way I'm sure I'm not creating the formula correctly. Example: I want to enter 01 for January, 02 for February and have the corresponding mont displayed in the cell I'm creating the formula in. Is this possibe if written in the same cell? Sounds pretty simple but I can't get an "IF" statement to work for me. It's been a loooong time since I last worked with Excel so I'd appreciate any help you can give. BTW: my office is 2002 Pro. |
#3
|
|||
|
|||
i want to create a formula to change a months name
As far as I know you can't get Excel to return the month with just a 2 digit
entry - how is Excel to know that you ment month 02 and not day 02? If you custom format the cell as "mmmm" (without the quotes), and enter 01/01 for January, 01/02 for February etc then you will get the month displayed. Incidentally XL will assume the year is the current year and the day part of the date can be any legal date. HTH Sandy -- to e-mail direct replace @mailintor.com with @tiscali.co.uk "change a month" wrote in message ... I want to create a formula to allow me to enter a # into a cell that will display a corresponding months name in that cell. In doing an IF statement I have been getting a cricular error, I assume because i'm creating the formula IN the refrence cell, or Excel can't find the project or library. Either way I'm sure I'm not creating the formula correctly. Example: I want to enter 01 for January, 02 for February and have the corresponding mont displayed in the cell I'm creating the formula in. Is this possibe if written in the same cell? Sounds pretty simple but I can't get an "IF" statement to work for me. It's been a loooong time since I last worked with Excel so I'd appreciate any help you can give. BTW: my office is 2002 Pro. |
#4
|
|||
|
|||
i want to create a formula to change a months name
This should allow you to type in a number 1 - 12, anything
else will show up as entered. Use of dateserial, I think would be slower and the example had no provision for errors. Private Sub Worksheet_Change(ByVal Target As Range) Dim x As Integer x = Target.Formula Application.EnableEvents = False 'should be part of Change macro On Error Resume Next Target.Value = Choose(x, "January", "February", "March", _ "April", "May", "June", "July", "August", "September", _ "October", "November", "December") done: If IsEmpty(Target.Value) Then Target.Formula = x Application.EnableEvents = True 'should be part of Change macro Exit Sub End Sub To install right click on the sheet tab, view code, insert your coding More on Event macros in http://www.mvps.org/dmcritchie/excel/event.htm watch out for the following problem, when working with change macros that terminate. http://www.mvps.org/dmcritchie/excel/event.htm#problems You'll probably want to change that the "Jan", "Feb", ... but this was what you asked for. I think you would get more out of newsgroups if you used your first and last name, both more friendly and more professional. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "change a month" wrote .. I want to create a formula to allow me to enter a # into a cell that will display a corresponding months name in that cell. Example: I want to enter 01 for January, 02 for February and have the corresponding month displayed in the cell I'm creating the formula in. |
#5
|
|||
|
|||
i want to create a formula to change a months name
Ripped out unwanted coding and threw out the
the check for the column (column 1 is column A), this should work better. Private Sub Worksheet_Change(ByVal Target As Range) Dim x As Integer if target.column 1 then exit sub x = Target.Formula Application.EnableEvents = False 'should be part of Change macro On Error Resume Next Target.Value = Choose(x, "January", "February", "March", _ "April", "May", "June", "July", "August", "September", _ "October", "November", "December") done: If IsEmpty(Target.Value) Then Target.Formula = x Application.EnableEvents = True 'should be part of Change macro Exit Sub End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "David McRitchie" wrote in message ... This should allow you to type in a number 1 - 12, anything else will show up as entered. Use of dateserial, I think would be slower and the example had no provision for errors. |
#6
|
|||
|
|||
i want to create a formula to change a months name
Thanks for the "Etiquette" advice and programming, much thanks David. I'm still a little skittish giving all that information out but maybe a decent, "User Name" would look and feel better
|
#7
|
|||
|
|||
i want to create a formula to change a months name
JE, thank you for the help, it did work. I thought, simple mindedly so apparently, it would be a matter of assigning a cell as a date then go from there. Again maybe a little simple mindedly so. With people like you and these others willing to help "newbies" such as my self, these responses puts a good light, almost a warm and fuzzy, on the "Geek-oid" myth.
Steven ----- JE McGimpsey wrote: ----- You can either have a value or a function in a cell, not both. When you enter a value in the cell, the formula is overwritten. You can use an event macro to change the value on entry. Put this in the worksheet code module (right click on the worksheet tab and choose view code): Private Sub Worksheet_Change(ByVal rCell As Excel.Range) With rCell If .Count 1 Then Exit Sub If .Address(False, False) = "A1" Then _ If IsNumeric(.Value) Then _ .Value = Format(DateSerial(2004, .Value, 1), "mmmm") End With End Sub Change A1 to your desired cell. In article , "change a month" wrote: I want to create a formula to allow me to enter a # into a cell that will display a corresponding months name in that cell. In doing an IF statement I have been getting a cricular error, I assume because i'm creating the formula IN the refrence cell, or Excel can't find the project or library. Either way I'm sure I'm not creating the formula correctly. Example: I want to enter 01 for January, 02 for February and have the corresponding mont displayed in the cell I'm creating the formula in. Is this possibe if written in the same cell? Sounds pretty simple but I can't get an "IF" statement to work for me. It's been a loooong time since I last worked with Excel so I'd appreciate any help you can give. BTW: my office is 2002 Pro. |
#8
|
|||
|
|||
i want to create a formula to change a months name
Sandy - thanks much for your input, really...thank you
Steven ----- Sandy Mann wrote: ----- As far as I know you can't get Excel to return the month with just a 2 digit entry - how is Excel to know that you ment month 02 and not day 02? If you custom format the cell as "mmmm" (without the quotes), and enter 01/01 for January, 01/02 for February etc then you will get the month displayed. Incidentally XL will assume the year is the current year and the day part of the date can be any legal date. HTH Sandy -- to e-mail direct replace @mailintor.com with @tiscali.co.uk "change a month" wrote in message ... I want to create a formula to allow me to enter a # into a cell that will display a corresponding months name in that cell. In doing an IF statement I have been getting a cricular error, I assume because i'm creating the formula IN the refrence cell, or Excel can't find the project or library. Either way I'm sure I'm not creating the formula correctly. Example: I want to enter 01 for January, 02 for February and have the corresponding mont displayed in the cell I'm creating the formula in. Is this possibe if written in the same cell? Sounds pretty simple but I can't get an "IF" statement to work for me. It's been a loooong time since I last worked with Excel so I'd appreciate any help you can give. BTW: my office is 2002 Pro. |
#9
|
|||
|
|||
i want to create a formula to change a months name
And another variant,
To JE and David: While I was working on this, both your replies came in and I must admit that I have stolen a bit from both of you. Using DateSerial, despise a possible slowdown, may have an advantage over hard coding the month names, in that it will return the month names in language of the system. On the other hand, if the output is to be in a specific language, hard coding is the way to go. My code is not any better than yours, but since I have it I can just as well post it. '***** Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim monthNum As Variant If Target.Cells.Count 1 Then Exit Sub If Intersect(Range("A:A"), Target) Is Nothing Then Exit Sub If IsNumeric(Target.Value) = False Then Exit Sub monthNum = Target.Value If monthNum 1 Or monthNum 12 Then Exit Sub Application.EnableEvents = False Target.Value = Format(DateSerial(1, monthNum, 1), "mmmm") Application.EnableEvents = True End Sub '***** Regards Anders Silven "change a month" skrev i meddelandet ... I want to create a formula to allow me to enter a # into a cell that will display a corresponding months name in that cell. In doing an IF statement I have been getting a cricular error, I assume because i'm creating the formula IN the refrence cell, or Excel can't find the project or library. Either way I'm sure I'm not creating the formula correctly. Example: I want to enter 01 for January, 02 for February and have the corresponding mont displayed in the cell I'm creating the formula in. Is this possibe if written in the same cell? Sounds pretty simple but I can't get an "IF" statement to work for me. It's been a loooong time since I last worked with Excel so I'd appreciate any help you can give. BTW: my office is 2002 Pro. |
#10
|
|||
|
|||
i want to create a formula to change a months name
This is another "improvement" on my code, checking the
IsNumeric from John's code. Private Sub Worksheet_Change(ByVal Target As Range) If Not IsNumeric(Target.Value) Then Exit Sub If Target.Value 1 Or Target.Value 12 Then Exit Sub Application.EnableEvents = False 'should be part of Change macro Target.Value = Choose(Target.Value, "January", "February", _ "March", "April", "May", "June", "July", "August", _ "September", "October", "November", "December") Application.EnableEvents = True 'should be part of Change macro End Sub I can find my postings on my email address which makes things easy. My name is unique enough that I can even search on my name to find my previous postings and replies. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Steven" wrote in message ... Thanks for the "Etiquette" advice and programming, much thanks David. I'm still a little skittish giving all that information out but maybe a decent, "User Name" would look and feel better |
|
Thread Tools | |
Display Modes | |
|
|