Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace
I have the following code:
CMonth = (Replace(Cells(13, 4).Value, "*", "???")) The only value that will be in Cells(13, 4) will be one of the 12 months. What I'm trying to do is truncate the month to just the 1st 3 chars. So if the month is January, CMonth = Jan. If it's October, CMonth = Oct. So on and so forth. The way I have it set up it's not working as intended though. What am I doing wrong? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace
Hi
Try this: cMonth = MonthName(Month(Cells(13, 4)), True) Regards, Per On 4 Aug., 20:34, Bishop wrote: I have the following code: CMonth = (Replace(Cells(13, 4).Value, "*", "???")) The only value that will be in Cells(13, 4) will be one of the 12 months. * What I'm trying to do is truncate the month to just the 1st 3 chars. *So if the month is January, CMonth = Jan. *If it's October, CMonth = Oct. *So on and so forth. *The way I have it set up it's not working as intended though. * What am I doing wrong? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace
Ok thanks for the idea. This is what I had to do to make it work:
Dim Month As Integer If Cells(13, 4).Value = "January" Then Month = 1 If Cells(13, 4).Value = "February" Then Month = 2 If Cells(13, 4).Value = "March" Then Month = 3 If Cells(13, 4).Value = "April" Then Month = 4 If Cells(13, 4).Value = "May" Then Month = 5 If Cells(13, 4).Value = "June" Then Month = 6 If Cells(13, 4).Value = "July" Then Month = 7 If Cells(13, 4).Value = "August" Then Month = 8 If Cells(13, 4).Value = "September" Then Month = 9 If Cells(13, 4).Value = "October" Then Month = 10 If Cells(13, 4).Value = "November" Then Month = 11 If Cells(13, 4).Value = "December" Then Month = 12 CMonth = MonthName(Month, True) But, I'm also trying to do the same thing with the year. The cell value is going to be 2009, 2010, 2011, etc. I just need the last 2 digits. How do I accomplish that? "Per Jessen" wrote: Hi Try this: cMonth = MonthName(Month(Cells(13, 4)), True) Regards, Per On 4 Aug., 20:34, Bishop wrote: I have the following code: CMonth = (Replace(Cells(13, 4).Value, "*", "???")) The only value that will be in Cells(13, 4) will be one of the 12 months. What I'm trying to do is truncate the month to just the 1st 3 chars. So if the month is January, CMonth = Jan. If it's October, CMonth = Oct. So on and so forth. The way I have it set up it's not working as intended though. What am I doing wrong? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace
If you just need the first three characters for the month name, try:
Dim cMonth As String cMonth = Left(Sheet3.Cells(13, 4).Value, 3) assuming it's okay to use a string for whatever purposes you have. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace
HI
I assumed you had a date in the cell. As you have a month name in the cell you can just use: cMonth=Left(cells(13,4),3) cYear=right(cells(13,5),2) ' Change cell reference to suit. Hopes this helps. .... Per On 4 Aug., 21:05, Bishop wrote: Ok thanks for the idea. *This is what I had to do to make it work: * * Dim Month As Integer * * If Cells(13, 4).Value = "January" Then Month = 1 * * If Cells(13, 4).Value = "February" Then Month = 2 * * If Cells(13, 4).Value = "March" Then Month = 3 * * If Cells(13, 4).Value = "April" Then Month = 4 * * If Cells(13, 4).Value = "May" Then Month = 5 * * If Cells(13, 4).Value = "June" Then Month = 6 * * If Cells(13, 4).Value = "July" Then Month = 7 * * If Cells(13, 4).Value = "August" Then Month = 8 * * If Cells(13, 4).Value = "September" Then Month = 9 * * If Cells(13, 4).Value = "October" Then Month = 10 * * If Cells(13, 4).Value = "November" Then Month = 11 * * If Cells(13, 4).Value = "December" Then Month = 12 * * CMonth = MonthName(Month, True) But, I'm also trying to do the same thing with the year. *The cell value is going to be 2009, 2010, 2011, etc. *I just need the last 2 digits. *How do I accomplish that? "Per Jessen" wrote: Hi Try this: cMonth = MonthName(Month(Cells(13, 4)), True) Regards, Per On 4 Aug., 20:34, Bishop wrote: I have the following code: CMonth = (Replace(Cells(13, 4).Value, "*", "???")) The only value that will be in Cells(13, 4) will be one of the 12 months. * What I'm trying to do is truncate the month to just the 1st 3 chars. *So if the month is January, CMonth = Jan. *If it's October, CMonth = Oct. *So on and so forth. *The way I have it set up it's not working as intended though. * What am I doing wrong?- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace
What is in D13... the full month name as text? If so, this one line will
replace all that code... CMonth = Left(Cells(13, 4).Value, 3) As for your question about the year, you didn't tell us what cell it is in (I'm guessing it can't be in D13). Assuming it is in E13, try this... CYear = Cells(13, 5).Value Mod 100 -- Rick (MVP - Excel) "Bishop" wrote in message ... Ok thanks for the idea. This is what I had to do to make it work: Dim Month As Integer If Cells(13, 4).Value = "January" Then Month = 1 If Cells(13, 4).Value = "February" Then Month = 2 If Cells(13, 4).Value = "March" Then Month = 3 If Cells(13, 4).Value = "April" Then Month = 4 If Cells(13, 4).Value = "May" Then Month = 5 If Cells(13, 4).Value = "June" Then Month = 6 If Cells(13, 4).Value = "July" Then Month = 7 If Cells(13, 4).Value = "August" Then Month = 8 If Cells(13, 4).Value = "September" Then Month = 9 If Cells(13, 4).Value = "October" Then Month = 10 If Cells(13, 4).Value = "November" Then Month = 11 If Cells(13, 4).Value = "December" Then Month = 12 CMonth = MonthName(Month, True) But, I'm also trying to do the same thing with the year. The cell value is going to be 2009, 2010, 2011, etc. I just need the last 2 digits. How do I accomplish that? "Per Jessen" wrote: Hi Try this: cMonth = MonthName(Month(Cells(13, 4)), True) Regards, Per On 4 Aug., 20:34, Bishop wrote: I have the following code: CMonth = (Replace(Cells(13, 4).Value, "*", "???")) The only value that will be in Cells(13, 4) will be one of the 12 months. What I'm trying to do is truncate the month to just the 1st 3 chars. So if the month is January, CMonth = Jan. If it's October, CMonth = Oct. So on and so forth. The way I have it set up it's not working as intended though. What am I doing wrong? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace
Thanks, Guys! I used Arjen Van and Your ideas for the Cmonth and Per
Jessen's idea for CYear. The following code works as intended: CMonth = Left(Cells(13, 4).Value, 3) CYear = Right(Cells(13, 7).Value, 2) Thanks again. "Rick Rothstein" wrote: What is in D13... the full month name as text? If so, this one line will replace all that code... CMonth = Left(Cells(13, 4).Value, 3) As for your question about the year, you didn't tell us what cell it is in (I'm guessing it can't be in D13). Assuming it is in E13, try this... CYear = Cells(13, 5).Value Mod 100 -- Rick (MVP - Excel) "Bishop" wrote in message ... Ok thanks for the idea. This is what I had to do to make it work: Dim Month As Integer If Cells(13, 4).Value = "January" Then Month = 1 If Cells(13, 4).Value = "February" Then Month = 2 If Cells(13, 4).Value = "March" Then Month = 3 If Cells(13, 4).Value = "April" Then Month = 4 If Cells(13, 4).Value = "May" Then Month = 5 If Cells(13, 4).Value = "June" Then Month = 6 If Cells(13, 4).Value = "July" Then Month = 7 If Cells(13, 4).Value = "August" Then Month = 8 If Cells(13, 4).Value = "September" Then Month = 9 If Cells(13, 4).Value = "October" Then Month = 10 If Cells(13, 4).Value = "November" Then Month = 11 If Cells(13, 4).Value = "December" Then Month = 12 CMonth = MonthName(Month, True) But, I'm also trying to do the same thing with the year. The cell value is going to be 2009, 2010, 2011, etc. I just need the last 2 digits. How do I accomplish that? "Per Jessen" wrote: Hi Try this: cMonth = MonthName(Month(Cells(13, 4)), True) Regards, Per On 4 Aug., 20:34, Bishop wrote: I have the following code: CMonth = (Replace(Cells(13, 4).Value, "*", "???")) The only value that will be in Cells(13, 4) will be one of the 12 months. What I'm trying to do is truncate the month to just the 1st 3 chars. So if the month is January, CMonth = Jan. If it's October, CMonth = Oct. So on and so forth. The way I have it set up it's not working as intended though. What am I doing wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
Error for a replace. It replace ALL instead of selected column | Excel Programming | |||
Replace text with variable using VBA replace code? | Excel Programming | |||
How to Replace multiple words to replace using excell | Excel Programming | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions |