Repeat 320 rows and then increment in excel 2007

[patelvin21]

i need a macro or formula for repeating 320 rows with the same value and then incrementing with next number and copy that for other 320 rows and so on,

which is like

ColumnA
1
1
1
2
2
2
...

here just i had shown for just 3rows which i need it for 320 rows to do using a macro or formula. And also i had used number here but it is a character in the field what i am using.

When i recorded using a macro function i got the following function,

Sub Repeat()
'
' Repeat Macro
'
' Keyboard Shortcut: Ctrl+Shift+B
'
Selection.AutoFill Destination:=Range("B18633:B18634"), Type:=xlFillDefault
Range("B18633:B18634").Select
Range("B18634").Select
Selection.AutoFill Destination:=Range("B18634:B18953"), Type:=xlFillCopy
Range("B18634:B18953").Select
ActiveWindow.SmallScroll Down:=300
Range("B18953").Select
End Sub

But with this i am able to do for particular range, i need it to do for 1400 times copying the same value in 320 rows and increment it and then again copy it.

Can any of u please help me.

 

[JackDanIce]

You'll need to adjust this code for the range etc but try:

Sub UnicornsShouldntExit ()
 
Dim i As Long, j As Long, k As Long
 
Application.ScreenUpdating = False
 
j = 1
k = Range("B" & j)

For i = 1 To 1400
    With Range("B" & j)
        .Value = k
        .Copy
        .Resize(320).PasteSpecial Paste:=xlPasteValues
        k = k + 1
    End With
    j = j + 320
Next i

Application.ScreenUpdating = True
 
End Sub

 

[JackDanIce]

Although I think if you repeat that code 1,400 times, you'll be trying to copy more than the number of rows Excel allows (I may be wrong about this)

 

[Weaver]

if all you wanted were the numbers, then

Sub test()
    For i = 1 To 1400
        Cells((i - 1) * 320 + 1, 1).Resize(320).Value = i
    Next
End Sub

Would work

If the values are non linear, maybe even text, then the approach may have to be different.

You may need to explain more fully what you are trying to achieve.

 

[Weaver]

Although I think if you repeat that code 1,400 times, you'll be trying to copy more than the number of rows Excel allows (I may be wrong about this)

It's okay Jack, 2007 has over 1,000,000 rows (2^20 to be exact), the OP requires about 450,000 for this task

 

[patelvin21]

Thanks you all for the immediate response.. i had tried JacDanIce code but here my value is "MP_CF_8.55_W153". Now this value is in column B and i want to repeat this one for 320 rows and in 321st row it should increment with "MP_CF_8.55_W154" and then copy for remaining 319 rows the same should repeat until my value ends with "MP_CF_8.55_W1400".

thanks for help.

 

[Weaver]

Something like?

Sub test()
    For i = 1 To 1400
        Cells((i - 1) * 320 + 1, 2).Resize(320).Value = "MP_CF_8.55_W" & i
    Next
End Sub

 

[patelvin21]

Thank you very much Weaver it worked. Thanks a lot for your help.