Copy number down cell using VBA

[akima]

Hi Wizards,

Can you help. I need the value of A6 to be copied down in the active cell 7 times up to number 52 then start again at 1 up to 52 using <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> e.g.

If A6 =5 and active cell is C20

C20 = 5
C21 = 5
C22 = 5
C23 = 5
C24 = 5
C25 = 5
C26 = 5
C27 = 6
C28 = 6
C29 = 6
C30 = 6
C31 = 6
C32 = 6
C33 = 6
C34 = 7
C35 = 7
C36 = 7
C37 = 7

etc up to 52 and start again at 1 up to 52.

This will need to happen whether I start from C20 or C31.

Thanks so much for your help.

 

[My Aswer Is This]

Try this:

Sub Active_Cell_Down()
Dim i As Long
Dim Lastrow As Long
Dim ans As Integer
ans = 52 - Cells(6, 1).Value + 1
Lastrow = ActiveCell.Row
    
    For i = 1 To ans
        Cells(Lastrow, ActiveCell.Column).Resize(7, 1).Value = Cells(6, 1).Value + i - 1
        Lastrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row + 1
    Next

End Sub

 

[akima]

Thank You.. Worked perfectly.

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thank You.. Worked perfectly.