A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

VBA code for current month



 
 
Thread Tools Display Modes
  #1  
Old May 6th, 2004, 04:22 PM
noah
external usenet poster
 
Posts: n/a
Default 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/

  #3  
Old May 6th, 2004, 04:40 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default 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  
Old May 6th, 2004, 04:43 PM
Arvi Laanemets
external usenet poster
 
Posts: n/a
Default 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  
Old May 6th, 2004, 04:53 PM
Arvi Laanemets
external usenet poster
 
Posts: n/a
Default 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  
Old May 6th, 2004, 08:44 PM
Don Guillett
external usenet poster
 
Posts: n/a
Default 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  
Old May 6th, 2004, 09:41 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default 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/





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:17 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.