Subject: Any body to crack it ?: Print Week Category based on range for mon
11/18/2008 4:58 AM PST

By: Ajith In: microsoft.public.excel.programming



"Ajith" wrote:

Hi,

Please Note that for my below query Column name for "Kth" i.e 11th Cloumn
is "Date" and Column Name for "Lth" i.e 12th Column is "Week1".

"Ajith" wrote:

Hi,

I am Ajith from Bangalore.
I have a date field in my excel sheet with field name week in Cloumn K
(11th Column). Format is date dd-mmm-yy (eg 3-Oct-08 )
with different dates.

I have a to categorise these date in column K(11) based on week.
If the date falls in week 1 of any month i.e from 1-7, then it should be
printed in the same row in L (12 th ) cloumn named as Week1.
similary if it falls in the 8th to 14 th of any month then Week2 should be
printed in Lth column of the same row.

Similarly for week3, week4 and week 5 in multiples of 7.

I have written a macro for this .Below is the code for this.
I am not able to crack the result even though the macro is not giving any
error.
Please help.

________________________________________________

Sub DateRangeforWeek()

Dim r As Long
Dim DayOfWeek As Long

For r = Cells(Rows.Count, 11).End(xlUp).Row To 2 Step -1

DayOfWeek = Day(Cells(r, 11))

'DayOfWeek =
WeekdayName(Weekday(ThisWorkbook.Sheets("HSOTLTime DetailsWS").Range("Date").Value), 0, 1)

If DayOfWeek 0 And DayOfWeek < 8 Then
Cells(r, 12) = "Week1"
End If
If DayOfWeek 7 And DayOfWeek < 15 Then
Cells(r, 12) = "Week2"
End If
If DayOfWeek 14 And DayOfWeek < 22 Then
Cells(r, 12) = "Week3"
End If
If DayOfWeek 21 And DayOfWeek < 29 Then
Cells(r, 12) = "Week4"
End If
If DayOfWeek 28 Then
Cells(r, 12) = "Week5"
End If
Next r

End Sub

Regards,
Ajith.