PDA

View Full Version : spread repated cells



amrane
11-14-2016, 01:48 AM
Dear forum,

I have blocking point, for which I not founding solution,

I have huge table in which I need to repeat some line with a given number, please refer to the attached xls file,

the opposite action is too simple, but what is the solution for my case

br,
amrane

Aussiebear
11-14-2016, 03:29 AM
=Rept(text,number)

mancubus
11-14-2016, 05:27 AM
if you need a vba solution, try;



Sub vbax_57711_copy_cells_values_n_times()

Dim i As Long

With Worksheets("Sheet1") 'change Sheet1 to suit
For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
.Range("E" & .Rows.Count).End(xlUp).Offset(1).Resize(.Range("B" & i)).Value = .Range("A" & i).Value
Next
End With

End Sub

amrane
11-18-2016, 01:41 AM
Dear Mancubus,

this professional solution, that's great,
thank you a lot,

Amrane,

Paul_Hossler
11-19-2016, 10:53 AM
Option Explicit
Sub Repeats()
Dim rControls As Range, rControl As Range
Dim iOut As Long, iNum As Long

Application.ScreenUpdating = False

'A2 to end of data, 2 columns
Set rControls = Range(ActiveSheet.Range("A2"), ActiveSheet.Range("A2").End(xlDown)).Resize(, 2)

iOut = 2
For Each rControl In rControls.Rows
For iNum = 1 To rControl.Cells(1, 2).Value
'starts in row 2 column 5
ActiveSheet.Cells(iOut, 5).Value = rControl.Cells(1, 1).Value
iOut = iOut + 1
Next iNum
Next
Application.ScreenUpdating = True
End Sub