VBA code for current month

  • Thread starter Thread starter noah
  • Start date Start date
N

noah

I am programing VBA code to add the current month and year to separat
columns. I need one column to display the current year, and another t
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 o
month?
Thanks
 
Maybe this will be quicker
Sub monyear()
Range("f1:f5000") = Format(Date, "mmm")
Range("g1:5000") = Format(Date, "yyyy")
End Sub
 
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)
 
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
 
But change timing formula to
MsgBox (et - st) / 1440

--
Don Guillett
SalesAid Software
(e-mail address removed)
Don Guillett said:
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
Click to expand...
 
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 said:
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
Click to expand...
 
He just likes you better. <G>

--
Don Guillett
SalesAid Software
(e-mail address removed)
Bob Phillips said:
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)
Click to expand...
 
Back
Top