Please help to correct syntex error

[james]

I am trying to creat a spreadsheet calendar for 2006 with the following VBA
code.

Dim i, j, k As Integer
For i = 1 To 12
dt = DateSerial(2005, i + 1, 1)
For j = 1 To Day(DateSerial(2006, i + 1, 0))
week_Text= Text(Date(2006,i,1),"ddd"))
Cells(i + 6, j).Value = week_Text
If week_Text = "Sat" Or week_Text = "Sun" Then
For k = i To i + 5
Cells(k, j).Select
With Selection.Interior
.ColorIndex = 48
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Next
Else
End If
Next
Next
End Sub

I got an error message on "week_Text= Text(Date(2006,i,1),"ddd"))".
Would anyone help?

Thanks in advance.

 

[JE McGimpsey]

Text() is a worksheet function that isn't available in VBA. Use the
Format() method instead.

 

[Dave Peterson]

But I saw a guy with initials JEM post something like:

Dim myDate As Date
myDate = Date
MsgBox Application.Text(myDate, "mm/dd/yyyy")

He didn't use this for one of the formats that VBA's Format has built in (that
JEM is way too smart for that), but he used it for a format that was available
in excel (using Text()).

I remember it 'cause I was doing something completely stupid at the time to get
to this!

 

[JE McGimpsey]

Obviously a smarter JEM than me...

 

[Dave Peterson]

Aw, that's not possible <gd&r>.

Obviously a smarter JEM than me...