Today I was writing a macro for somebody.
In the workbook the sheet names are month names e.g. july,august,September etc and 1st of each month is to be entered in a cell in each the sheets.
By some trial and error I wrote this macro
Sub test()
Dim j As Integer, monthnr As Long, monthname As String
For j = 1 To Worksheets.Count
With Worksheets(j)
.Range("a1").Delete
monthname = Worksheets(j).Name
Dim dte As Date
dte = monthname & "/1/2013"
If month(dte) < 7 Then
dte = monthname & "/1/2014"
End If
.Range("A1") = dte
End With
Next j
End Sub
Display More
This worked in July sheet it is written as 7/1/2013 and in august sheet it is 8/1/2013 etc
Then I shortened the macro like thlis
It worked. Even if I use shortened 3 letter form of month like “Jul” or “aug” then also it works
But if type on the spreadsheet itself
="july"/1/2013
It Gives value error.
Perhaps it works only in vba and not spreadsheet.
I accept nobody is going to use “july” & “/1/2013” instead of 7/1/2013
But this will be useful if sheet names are month names so that when we write the date we can use sheet names
This is new to me (may not be new for experts)
Mine excel 2007 and windows 7
This may be useful for those who are not experts in excel and vba