Macro that displays all the weeks between Start Week and End Week (ends in next year)

**Tona** · 10-30-2014, 07:38 AM

I have production planning Sheet (Week.xlsx)

In column A I have starting week of the production, in column B I have end week of production.

What I would like is to get is macro, that will display all the weeks between "start week" and "end week".

I have macro that displays all the numbers between stated figures (eg. 32-36, it inputs 32, 33, 34, 35, 36), but if my start week is 52 which is the last week in the year I would like macro to display eg. 52-3, 52, 1, 2, 3.

Please see attached file.
Many thanks.

**sktneer** · 10-30-2014, 08:07 AM

Please find the attached sheet and click on the Green Button on Test Sheet to see if you get the desired output.

**Tona** · 10-30-2014, 08:16 AM

Thank you sktneer.

It works if cell is exact 52.
But it won't work if start week is eg. 48 or 50 or 51 and end week 3?
So every time it exceeds 52 or if it includes 52 should go from 1.

**sktneer** · 10-30-2014, 08:43 AM

Please find the attached sheet to see if this works as per your requirement now.

**Tona** · 10-30-2014, 08:57 AM

Perfect! Thank you.

Will try to understand it now.

**sktneer** · 10-30-2014, 09:20 AM

You're welcome.

You may also click on * (star) to Add Reputation if the solution provided helped you. This is another way to say thanks.

**Tona** · 10-31-2014, 04:21 AM

When I modify macro for my needs, I get "debug":

Sub Test1()
Dim rng As Range, cell As Range
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("FL3:FL" & lr)
For Each cell In rng
    If cell <> "" And cell > cell.Offset(0, 1) Then
        For i = 0 To 52 - cell + cell.Offset(0, 1)
            If cell + i > 52 Then
                Cells(cell.Row, 170 + i) = (cell + i) Mod 52
            Else
                Cells(cell.Row, 170 + i) = cell + i
            End If
        Next i
    Else
        For i = 0 To cell.Offset(0, 1) - cell
            Cells(cell.Row, 170 + i) = cell + i
        Next i
    End If
Next cell
End Sub

Any ideas why?

**sktneer** · 10-31-2014, 05:38 AM

Not sure unless I see your sheet.

BTW change this line of code

lr = Cells(Rows.Count, 1).End(xlUp).Row

To

lr = Cells(Rows.Count, "FL").End(xlUp).Row

Macro that displays all the weeks between Start Week and End Week (ends in next year)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro that displays all the weeks between Start Week and End Week (ends in next year)

Re: Macro that displays all the weeks between Start Week and End Week (ends in next year)

Re: Macro that displays all the weeks between Start Week and End Week (ends in next year)

Re: Macro that displays all the weeks between Start Week and End Week (ends in next year)

Re: Macro that displays all the weeks between Start Week and End Week (ends in next year)

Re: Macro that displays all the weeks between Start Week and End Week (ends in next year)

Re: Macro that displays all the weeks between Start Week and End Week (ends in next year)

Re: Macro that displays all the weeks between Start Week and End Week (ends in next year)

Thread Information

Users Browsing this Thread

Similar Threads

week number based on year start 01/10

[SOLVED] Determine Week Start Date From Year And Number

Fri last week, 2 weeks ago, 6 months ago, 1 year ago BUT if it falls on holiday???

[SOLVED] IF statment to bring back RAG status on week end between multiple week-ends

Insert a new row when week ends / week starts?

Bookmarks

Bookmarks

Posting Permissions