Help on generating a number series using a macro

[Solved/Closed]
Report
Posts
1
Registration date
Monday November 12, 2012
Status
Member
Last seen
November 12, 2012
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
basic data are in columns A TO c IN rows 1 to 4