Repeat value in column A by number in column B in column C

[sportjunkie42]

I'm looking for an easy way to repeat a value in column A by a number in column B and put those results in column C.

Before:

Josh 3
Lee 1
John 2

After:
Josh
Josh
Josh
Lee
John
John

Amy help would be greatly appreciated.

 

[isabelle]

hi,

Sub Macro1()
Dim wks1 As Worksheet, wks2 As Worksheet
Set wks1 = Worksheets("Sheet1")
Set wks2 = Worksheets("Sheet2")

For i = 1 To wks1.Cells(wks1.Rows.Count, 1).End(xlUp).Row
With wks2
LastCell = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
wks1.Cells(i, 1).Copy .Range(.Cells(LastCell, 1), .Cells(LastCell +
wks1.Cells(i, 2) - 1, 1))
Application.CutCopyMode = False
End With
Next
End Sub

isabelle

Le 2013-08-16 18:39, (e-mail address removed) a écrit :

 

[isabelle]

oops : put those results in column C

Sub Macro1()
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To Cells(Cells.Rows.Count, 1).End(xlUp).Row
LastCell = Cells(Cells.Rows.Count, 3).End(xlUp).Row + 1
Cells(i, 1).Copy Range(Cells(LastCell, 3), Cells(LastCell + Cells(i,
2) - 1, 3))
Application.CutCopyMode = False
Next
Range("C1").Delete Shift:=xlUp
Application.ScreenUpdating = True
End Sub

isabelle

 

[isabelle]

another way without using copy

Sub Macro2()
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To Cells(Cells.Rows.Count, 1).End(xlUp).Row
LastCell = Cells(Cells.Rows.Count, 3).End(xlUp).Row + 1
Range(Cells(LastCell, 3), Cells(LastCell + Cells(i, 2) - 1, 3)).Value
= Cells(i, 1)
Next
Range("C1").Delete Shift:=xlUp
Application.ScreenUpdating = True
End Sub

isabelle

 

[isabelle]

ho yes much better,
thank you Ron for this code,

isabelle

Le 2013-08-16 22:51, Ron Rosenfeld a écrit :

 

[isabelle]

hi Ron,

this is the first time i see a Redim with 2 dimentions
and i trying to understand, so i changed
ReDim lips (1TB WorksheetFunction.Sum (rSrc.Columns (2)), 1 To 2)
by
ReDim lips (1TB WorksheetFunction.Sum (rSrc.Columns (2)), 1 To 1)
and everything works well, please could you explain to me why 2 dimentions?

isabelle

Le 2013-08-16 22:51, Ron Rosenfeld a écrit :

 

[isabelle]

i realize that my question may bring to confusion, my question is about
you added a second argument

Le 2013-08-17 00:40, isabelle a écrit :

 

[GS]

this is the first time i see a Redim with 2 dimentions

and i trying to understand, so i changed
ReDim lips (1TB WorksheetFunction.Sum (rSrc.Columns (2)), 1 To 2)
by
ReDim lips (1TB WorksheetFunction.Sum (rSrc.Columns (2)), 1 To 1)
and everything works well, please could you explain to me why 2 dimentions?

Hi Isabelle,
I don't mean to butt in here but maybe you'll find the following
helpful...

A worksheet is a 2D array of cells; Dim1 = num rows, dim2=num cols.
This is by default what you get when you 'dump' a range into a Variant,
and so vSrc(1 To RowCount, 1 To ColCount) is the resulting size of the
array.

Row/col indexes start at 1 and so vSrc(1, 1) contains the value of
row1,col1. vSrc(1, 2) contains the value of row1, col2.

Redim vRes(1 To 6, 1 To 2) results a 6 row by 2 col 2D array that can
be 'dumped' back into the worksheet without the need to Transpose. The
Transpose function has limitations that, though well beyond this
scenario, avoiding the need of it is a good idea when the array needs
to go back into a sheet.

Ron's loop returns a 2-col array where the 2nd col is blank. Your Redim
to 1 col has the same effect except the OP wanted no values in col2 and
Ron's use of '1 To 2' for the 2nd dim clears the values in col2 when
the array is assigned back to A1:B6.

HTH

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Actually, the 2-col array was a mistake, and it should have been a 1-col
array. The results are getting dumped to col c. The error had no apparent
effect because the range to which the array was being assigned was a 1-col
array, so the 2nd column was ignored.

Yes, after rereading the OP I see my err! Thanks for pointing out that
the destination col is C (how did I miss that?), and so 1 To 1 should
indeed be the 2nd dim. My explanation is only good if the original data
is to be replaced!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[isabelle]

ok thank you Ron and Garry for those explanations, now i understand that
the two dimentions are necessary to tranpose on the sheet

isabelle

Le 2013-08-17 07:30, Ron Rosenfeld a écrit :

Here is a better version of my original submission. It corrects a minor error (that had no effect on the process)

-- see my comments to Isabelle and GS, and also clears column C before
writing the results of the repetitions.

 

[sportjunkie42]

Thanks Ron, Isabelle, and everyone else that chimed in! This is going to save me tons of time!!!