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("HSOTLTimeDetailsWS").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.
|
Similar Threads
