Repeating cells in specific order

[ohlite]

Hello all, thanks for taking the time to view and hopefully answer my problem. I have 589 cells in column B. In cell B1 is the column heading. It can stay right where it is and needs nothing to be done to it. In cells B2 to B589 i have a series of 5 digit numbers that I need to repeat ten times each. Not repeating the cells over and over ten times, but repeating a cell ten times before moving to the next. In other words:

• the number in B2 will be in cells B2 to B11
• the number in B3 will now be in cells B12 to B21.
• the number in B4 will be in B22 to B31 and so on
• Until the last number in cell B589 will be in cells B5872 to B5881

Sorry that was repeatative but I have posted on several sites and not explained it well and haven't been able to get a correct answer. If you need more info or would like me to attach the file I'm working on to have a look at, please don't hestitate to ask. I've been working and trying to get this to work for hours and can't figure it out. Thanks for any help you can give.

 

[shg]

Welcome to the board.

If the number in cell B2 is in B2 through B11, then the value in B3 is the same as B2. Continue that and all the cells have the same value.

What am I missing?

 

[VoG]

Try this

Sub atest()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    With Range("B" & i)
        .Copy
        .Resize(9).Insert shift:=xlShiftDown
    End With
Next i
Application.CutCopyMode = False
End Sub

 

[shg]

If the values in B2:B11 are unique, and the values start repeating in B12, then in B12 and copy down,

=INDEX(B:B,INT((ROW()+8)/10))

 

[ohlite]

SHG: The cells B2 to B589 are all unique. I need to make them cells B2 to cells B5881 by repeating the data in cell B2 to be in cells B2 to B11 and so on until I reach cell B5881. Hope that helps, I'm having trouble explaining it. I do thank you for any help you can give.

Vog: Im going to try your macro and reply when done. Thanks for giving it a try.

 

[ohlite]

Holy crap!!!! It worked!!! I have spent so many hours on this, thank you so very much VoG. I would love to be able to understand how this macro works with a line by line break down if you can, but if not I understand. Thanks to shg for giving it a crack as well. I can't believe there isn't a formula that would do this same thing. I don't understand macro's at all and would prefer to use a formula if one was available. You guys rock!!! Thanks for making my day and please keep up the great work helping us, the computer idiots, I sure your work will never be done.

 

[shg]

In that case, I think Peter's code does what you want.

 

[VoG]

Any help?

Sub atest()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row ' get the last row
For i = LR To 2 Step -1 ' loop backwards from the last row ti row 2
    With Range("B" & i)
        .Copy 'copy the value
        .Resize(9).Insert shift:=xlShiftDown ' 'insert 9 rows and by default copy down the value
    End With
Next i
Application.CutCopyMode = False
End Sub

 

[shg]

You can do it with a formula, but in needs to go in a separate column. In C2 and copy down,

=INDEX($B$2:$B$589, INT((ROWS(B$2:B2)+9)/10))

You could copy that, replace with values, and then delete col B.