Is there a way to compact this?

[geecee]

Is there any way in which the code can be compacted? I have up to 20 weeks.

Code: Select all

 Select Case week
    Case 1: Range("C3") = m(1): Range("C4") = m(2): Range("C5") = m(3)
: Range("C6") = m(4): Range("C7") = m(5)
    Case 2: Range("D3") = m(6): Range("D4") = m(7): Range("D5") = m(8)
: Range("D6") = m(9): Range("D7") = m(10)
    Case 3: Range("E3") = m(11): Range("E4") = m(12): Range("E5") = m(13)
: Range("E6") = m(14): Range("E7") = m(15)
End Select

Any help will be appreciated. Thanks.

[HansV]

Declare a variable i:

Code: Select all

    Dim i as Long

Instead of the Select week ... End Select block you can then use

Code: Select all

    For i = 0 To 4
       Cells(i + 3, week + 2) = m(week + i)
    Next i

If you want to loop over the weeks:

Code: Select all

    For week = 1 To 20
        For i = 0 To 4
           Cells(i + 3, week + 2) = m(week + i)
        Next i
    Next week

[geecee]

Thanks for your reply Hans. Unfortunately it does not do what I require. Perhaps I should have been a bit more explicit.

I have a list of 100 items. What I want to do is break it down into 20 groups of 5 with the first having items 1 to 5, the second having items 6 to 10, the third having items 11 to 15 and so on.

[HansV]

That is what my version of the code does, as far as I can tell.

[geecee]

Thanks Hans but this is what I get

ATEST.xlsm

The first group is OK but the second group includes 4 of the first plus one other, the third group includes 4 of the second group plus one other and so on it goes and only 24 of the 100 items have been used.

[HansV]

Sorry, I see my mistake. Try this version:

Code: Select all

Sub MOVIE_TITLES()
'=================================================
'   LIST MOVIES UNDER APPROPRIATE WEEKS
'=================================================
    Dim week As Long
    Dim i As Long
    For week = 1 To 20
        For i = 0 To 4
           Cells(i + 3, week + 2) = Cells(5 * week + i - 4, 1)
        Next i
    Next week
End Sub

[geecee]

Thanks Hans. Much appreciated.