PDA

View Full Version : [SOLVED] macro to repeat a value times in a column



elsg
09-22-2013, 05:39 PM
Hei
I need a macro to repeat a value (5) times in a column


Before


mary


john


vok






After


mary


mary


mary


mary


john


john


john


john


john


vok


vok


vok


vok


vok

nilem
09-22-2013, 07:55 PM
Hi

Sub ertert()
Dim x, y(), tm&, i&, j&, k&: tm = 5
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
x = .Value: ReDim y(1 To UBound(x) * tm, 1 To 1)
For i = 1 To UBound(x)
For j = 1 To tm
k = k + 1: y(k, 1) = x(i, 1)
Next j
Next i
.Resize(k).Value = y()
End With
End Sub

mrojas
09-22-2013, 08:58 PM
Wow. Although the code provided by nilem is very concise and may very well work , it is as cryptic as e=mc2. Code comments would greatly enhance the code and help those that ask for help, and those who read posts, learn how things work thus helping them help themselves in the future.

elsg
09-23-2013, 04:45 AM
was great!!

thank you very much!!
be able to comment on the code, thank you!

EirikDaude
09-23-2013, 10:40 PM
Here's a thoroughly commented solution to the problem if anyone's still interested:


Option Explicit
Option Base 1

Sub copyNames()
Dim names() As String, numberOfCopies As Integer, rangeToCheck As Range, cLoopCounter As Range, iLoopCounter As Integer
Dim adjustment As Integer

numberOfCopies = 5 ' The number of times we want to copy each name

' First we set what worksheet it is we are working in, to avoid a bit of typing later, and to make the sub look a bit tidier
' Change what's inside the parenthesis to whatever your worksheet is named.
With Worksheets("Sheet1")
' Then we set the range in which we look for names
Set rangeToCheck = Range(.Range("A1"), .Cells(Rows.Count, "A").End(xlUp))
' Next we set the size of the name()-array. We need the array to have as many possible entries
' as there are cells in the range:
ReDim names(1 To rangeToCheck.Count)
' Loop through each cell in the range, adding the names to the array names()
For Each cLoopCounter In rangeToCheck
' We use a counter as the index for the array
iLoopCounter = iLoopCounter + 1
' Set the value of the current index of the array to be the value of the cell we're at
names(iLoopCounter) = cLoopCounter.value
Next
' Now we have all the names originally on the sheet stored in the array and can start writing them to the sheet.
' As shown in the solution above, this could have been accomplished within one loop, but I think this solution is a bit tidier.
With .Range("A1")
For iLoopCounter = 1 To UBound(names)
' We'll offset where we start putting in names by a equal to the number of names we've already put in:
adjustment = (iLoopCounter - 1) * numberOfCopies
' Then enter the name at the current position in the array into the cells we're at.
Range(.Offset(adjustment, 0), .Offset(adjustment + numberOfCopies - 1, 0)).value = names(iLoopCounter)
Next
End With
End With
End Sub

elsg
09-24-2013, 05:41 AM
Very good!!!!

Thank you!!!!!!

mrojas
09-24-2013, 07:34 AM
Well done Eirik. Thank you.