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
|
|||
|
|||
VBA code for current month
I am programing VBA code to add the current month and year to separate
columns. I need one column to display the current year, and another to display the current month in text format. EXAMPLE: May, June, July.... I set this up: For i = 1 To 5000 Cells(i, 14).Value = DateTime.Month(Now()) Cells(i, 15).Value = DateTime.Year(Now()) Next i It works great. But my month field is displayed by number. EXAMPLE: 5, 6, 7.... Does anyone know how I can change this code to display the full text on month? Thanks, --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
VBA code for current month
Maybe this will be quicker
Sub monyear() Range("f1:f5000") = Format(Date, "mmm") Range("g1:5000") = Format(Date, "yyyy") End Sub -- Don Guillett SalesAid Software "noah " wrote in message ... I am programing VBA code to add the current month and year to separate columns. I need one column to display the current year, and another to display the current month in text format. EXAMPLE: May, June, July.... I set this up: For i = 1 To 5000 Cells(i, 14).Value = DateTime.Month(Now()) Cells(i, 15).Value = DateTime.Year(Now()) Next i It works great. But my month field is displayed by number. EXAMPLE: 5, 6, 7.... Does anyone know how I can change this code to display the full text on month? Thanks, --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
VBA code for current month
For i = 1 To 5000 Cells(i, 14).Value = Format(Date,"yyyy") Cells(i, 15).Value = Foramt(Date,"mmmm") Next i -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "noah " wrote in message ... I am programing VBA code to add the current month and year to separate columns. I need one column to display the current year, and another to display the current month in text format. EXAMPLE: May, June, July.... I set this up: For i = 1 To 5000 Cells(i, 14).Value = DateTime.Month(Now()) Cells(i, 15).Value = DateTime.Year(Now()) Next i It works great. But my month field is displayed by number. EXAMPLE: 5, 6, 7.... Does anyone know how I can change this code to display the full text on month? Thanks, --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
VBA code for current month
Hi
Cells(i, 14).Value = Format(DateTime.Month(Now()),"mmmm") -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "noah " wrote in message ... I am programing VBA code to add the current month and year to separate columns. I need one column to display the current year, and another to display the current month in text format. EXAMPLE: May, June, July.... I set this up: For i = 1 To 5000 Cells(i, 14).Value = DateTime.Month(Now()) Cells(i, 15).Value = DateTime.Year(Now()) Next i It works great. But my month field is displayed by number. EXAMPLE: 5, 6, 7.... Does anyone know how I can change this code to display the full text on month? Thanks, --- Message posted from http://www.ExcelForum.com/ |
#5
|
|||
|
|||
VBA code for current month
Sorry!
Arvi Laanemets "Arvi Laanemets" wrote in message ... Hi Cells(i, 14).Value = Format(DateTime.Month(Now()),"mmmm") -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "noah " wrote in message ... I am programing VBA code to add the current month and year to separate columns. I need one column to display the current year, and another to display the current month in text format. EXAMPLE: May, June, July.... I set this up: For i = 1 To 5000 Cells(i, 14).Value = DateTime.Month(Now()) Cells(i, 15).Value = DateTime.Year(Now()) Next i It works great. But my month field is displayed by number. EXAMPLE: 5, 6, 7.... Does anyone know how I can change this code to display the full text on month? Thanks, --- Message posted from http://www.ExcelForum.com/ |
#6
|
|||
|
|||
VBA code for current month
When you have some time, try these on a blank workbook. Both work 12 sec vs
NOW. Sub foreach() st = Time For i = 1 To 5000 Cells(i, 1).Value = Format(Date, "yyyy") Cells(i, 2).Value = Format(Date, "mmmm") Next i et = Time MsgBox Format((et - st) * 1440, "mm:ss") End Sub Sub byrange() st = Time Range("c1:c5000") = Format(Date, "mmm") Range("d1:c5000") = Format(Date, "yyyy") et = Time MsgBox Format((et - st) * 1440, "ss") End Sub -- Don Guillett SalesAid Software "noah " wrote in message ... Thanks, Bob Phillips It worked! --- Message posted from http://www.ExcelForum.com/ |
#8
|
|||
|
|||
VBA code for current month
Don't know why he didn't pick up on yours, it hit the NG earlier, so you
would assume that would be the first he tried(?). -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Don Guillett" wrote in message ... When you have some time, try these on a blank workbook. Both work 12 sec vs NOW. Sub foreach() st = Time For i = 1 To 5000 Cells(i, 1).Value = Format(Date, "yyyy") Cells(i, 2).Value = Format(Date, "mmmm") Next i et = Time MsgBox Format((et - st) * 1440, "mm:ss") End Sub Sub byrange() st = Time Range("c1:c5000") = Format(Date, "mmm") Range("d1:c5000") = Format(Date, "yyyy") et = Time MsgBox Format((et - st) * 1440, "ss") End Sub -- Don Guillett SalesAid Software "noah " wrote in message ... Thanks, Bob Phillips It worked! --- Message posted from http://www.ExcelForum.com/ |
#9
|
|||
|
|||
VBA code for current month
He just likes you better. G
-- Don Guillett SalesAid Software "Bob Phillips" wrote in message ... Don't know why he didn't pick up on yours, it hit the NG earlier, so you would assume that would be the first he tried(?). -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Don Guillett" wrote in message ... When you have some time, try these on a blank workbook. Both work 12 sec vs NOW. Sub foreach() st = Time For i = 1 To 5000 Cells(i, 1).Value = Format(Date, "yyyy") Cells(i, 2).Value = Format(Date, "mmmm") Next i et = Time MsgBox Format((et - st) * 1440, "mm:ss") End Sub Sub byrange() st = Time Range("c1:c5000") = Format(Date, "mmm") Range("d1:c5000") = Format(Date, "yyyy") et = Time MsgBox Format((et - st) * 1440, "ss") End Sub -- Don Guillett SalesAid Software "noah " wrote in message ... Thanks, Bob Phillips It worked! --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|