Repeat values in a list x number of times
 

On sheet 1 I have numbers in A2 to A50, (about 50 or 55 maybe 60 numbers.)

1230
1001
2354
.....
.....
etc.

On sheet 2 I want to repeat each number 7 times in A2 to A356 (or there about).
Seven of each value on sheet 2 for as many rows as it takes.

The numbers are unique, but would be helpful to know if it will work if there are some duplicates, or what problems to look out for if there are duplicates.

To solve for unique values only is first priority.

Thanks,
Howard

Repeat values in a list x number of times
 

Hi Howard,

Am Fri, 19 Feb 2016 16:16:58 -0800 (PST) schrieb L. Howard:

On sheet 1 I have numbers in A2 to A50, (about 50 or 55 maybe 60 numbers.)

1230
1001
2354
....
....
etc.

On sheet 2 I want to repeat each number 7 times in A2 to A356 (or there about).
Seven of each value on sheet 2 for as many rows as it takes.

The numbers are unique, but would be helpful to know if it will work if there are some duplicates, or what problems to look out for if there are duplicates.

To solve for unique values only is first priority.

try:

Sub Test()
Dim LRow As Long, i As Long, n As Long
Dim varData As Variant, varTmp As Variant
Dim myDic As Object

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varData = .Range("A2:A" & LRow)
'unique values
Set myDic = CreateObject("Scripting.Dictionary")
For i = LBound(varData) To UBound(varData)
myDic(varData(i, 1)) = varData(i, 1)
Next
varTmp = myDic.items
End With

n = 2
For i = LBound(varTmp) To UBound(varTmp)
Sheets("Sheet2").Cells(n, 1).Resize(7) = varTmp(i)
n = n + 7
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Repeat values in a list x number of times
 

To solve for unique values only is first priority.

try:

Sub Test()
Dim LRow As Long, i As Long, n As Long
Dim varData As Variant, varTmp As Variant
Dim myDic As Object

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varData = .Range("A2:A" & LRow)
'unique values
Set myDic = CreateObject("Scripting.Dictionary")
For i = LBound(varData) To UBound(varData)
myDic(varData(i, 1)) = varData(i, 1)
Next
varTmp = myDic.items
End With

n = 2
For i = LBound(varTmp) To UBound(varTmp)
Sheets("Sheet2").Cells(n, 1).Resize(7) = varTmp(i)
n = n + 7
Next
End Sub


Regards
Claus B.
--


You're the Boss! Perfect.

Thanks, Claus.

Howard