Did I drive my De Lorean to work? Why does this macro think it is January 2005?

[jmthompson]

Good morning,
I have created the Macro below to delete all columns from P-last column whose header is not Month-Year (this month should be June-08). I've tested this code with columns whose header is text and with columns whose header is entered as a date and formatted date Month-yy and a combination of the two, either way it deletes all columns from P to the last column. I've tried stepping through the code, and when I hover over MyMonth in the If statement, January appears. When I hover over MyYear, 2005 appears.

Any ideas?

Sub MyColumn()
 
MyMonth = Format(Month(Now), "mmmm")
MyYear = Format(Year(Now), "yy")
LastColumn = Range("IV1").End(xlToLeft).Column
For i = LastColumn To 16 Step -1
If (Cells(1, i).Value) <> MyMonth & "-" & MyYear Then
Cells(1, i).EntireColumn.Delete
End If
Next i

End Sub

 

[Jonmo1]

Change

MyMonth = Format(Month(Now), "mmmm")
MyYear = Format(Year(Now), "yy")

to

MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yy")

 

[Richard Schollar]

You don't need the Year or the Month function in there ie:

MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yy")

Both of these could additionally be shortened to:

MyMonthandYear = Format(Date,"mmmm-yy")

 

[RoryA]

Try:

Sub MyColumn()
 
MyDate = Format(Date, "mmmm-yy")
LastColumn = Range("IV1").End(xlToLeft).Column
For i = LastColumn To 16 Step -1
If (Cells(1, i).Text) <> MyDate Then
Cells(1, i).EntireColumn.Delete
End If
Next i

End Sub

VBA and Excel treat dates as days from 1/1/1900. Month(Now) returns 6. If you try and format that as "mmmm", VBA tries to interpret 6 as a date, which it treats as 6 Jan 1900. For year(Now) you get 2008, then formatted as year you get 2008 days from 1/1/1900 which is 30 June 1905.
HTH

 

[jmthompson]

Thank you, both for the solutions!

I think I'm going to order Mr Excel's book now

 

[jmthompson]

Thanks, rory, for the further explanation. Makes sense now!