Change Day to Month - VBA

[Guest]

Hi,

I got this macro from this NG to promt a password for a particular "day".
How do you change the macro to a particular "month and year".

from (#9/10/2007#) to Sep 2007
from (#9/11/2007#) to Oct 2007
from (#9/12/2007#) to Nov 2007

The format from vba help, "month(#9/10/2007#)" did not work.

Sub PWforDate()
Dim arrPwords(1 To 3, 1 To 2) As Variant
Dim strPWord As String

arrPwords(1, 1) = (#9/10/2007#)
arrPwords(2, 1) = (#9/11/2007#)
arrPwords(3, 1) = (#9/12/2007#)
arrPwords(1, 2) = 1234
arrPwords(2, 2) = 2345
arrPwords(3, 2) = 3456
strPWord = CStr(Application.VLookup(Date, arrPwords, 2, 0))
MsgBox strPWord
End Sub

Thank you.

 

[Jason]

It looks like you could change (#9/10/2007#) to ("Sep 2007"), etc. and
then change strPWord = CStr(Application.VLookup(Date, arrPwords, 2,
0)) to strPWord = CStr(Application.VLookup(format(Date,"mmm yyyy"),
arrPwords, 2, 0)).

Hope this helps.
Jason

 

[Guest]

Hi Jason,

When I changed it to ("Sep 2007"), it automatically changes to (#9/1/2007#).
When I ran the macro, it pops up, Error 2042.

Can you please take a look at again? Thank you.

 

[Jason]

Danny,
This appears to work for me:

Sub PWforDate()
Dim arrPwords(1 To 3, 1 To 2) As Variant
Dim strPWord As String

arrPwords(1, 1) = ("Sep 2007")
arrPwords(2, 1) = ("Oct 2007")
arrPwords(3, 1) = ("Nov 2007")
arrPwords(1, 2) = 1234
arrPwords(2, 2) = 2345
arrPwords(3, 2) = 3456
strPWord = Application.VLookup(Format(Date, "mmm yyyy"), arrPwords, 2,
0)
MsgBox strPWord
End Sub

If you copy and paste this into a module, does it still give you that
error?

 

[Guest]

Hi Jason,

This time it worked perfectly. Just taking out the "CStr" before the
Applicatio.Vlookup did it.

Thanks a lot!