Previous Topic Next Topic

Help on generating a number series using a macro

Solved/Closed
sbhambra Posts 1 Registration date Monday November 12, 2012 Status Member Last seen November 12, 2012 - Nov 12, 2012 at 06:38 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Nov 15, 2012 at 07:53 PM
Hello,
I would appreciate your help with a macro I am trying to create in Excel 2010
my Data is as below
Column1 Column2 Column3
(Name) (start number) (number of steps of increment)
ie
Column1 Column2 Column3
Joe 0001 7
David 0008 43
Jane 0051 5
peter 0055 5

What I'm trying to get is the series of numbers in each row ie 0002, 0003, 0004, etc to 0007 output as

Joe 0001
Joe 0002
Joe 0003
Joe 0004
Joe 0005
Joe 0006
Joe 0007
David 0008
David 0009
...
Peter 0055
Peter 0056
etc..

Can any one help?

Regards



2 responses

Answer 1 / 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Nov 15, 2012 at 07:51 PM
try this macro (result will be in columns F and G 

Sub test()
Dim r As Range, c As Range, dest As Range, x As Long, y As Long

Range(Range("F2"), Range("F2").End(xlToRight)).EntireColumn.Delete
Set r = Range(Range("C1"), Range("C1").End(xlDown))
For Each c In r
x = c.Value
Set dest = Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)
Range(dest, dest.Offset(x - 1, 0)).FormulaArray = Cells(c.Row, "A")

Next c
 Range("G2") = 1
 y = WorksheetFunction.Sum(r)
    

Range(Range("G2"), Range("G2").Offset(y - 1, 0)).DataSeries , Step:=1, Trend:=False
End Sub
0
Answer 2 / 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Nov 15, 2012 at 07:53 PM
basic data are in columns A TO c IN rows 1 to 4
0